CODEWITHSUNDEEP
mysqlquery-optimization
Chris Muench
Chris Muench

Reputation: 18318

mysql file sort happens even with indexes -- How can I fix

I have a simple query below that I can to make sure runs fast if the table. I did an explain on the query and it says Using where; Using file sort. Is there a way to get rid of the file sort? The data has only about 25 items in it now; but it could end up by 300 or more.

mysql> show create table phppos_categories;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `phppos_categories_ibfk_1` (`parent_id`),
  KEY `name` (`name`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-----------+-------------------+
| id | parent_id | name              |
+----+-----------+-------------------+
|  3 |         5 | Basketball Shoes  |
|  7 |         5 | Basketball Shorts |
+----+-----------+-------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM (`phppos_categories`) WHERE `parent_id` = 5 ORDER BY `name` asc;
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
| id | select_type | table             | type | possible_keys                      | key                      | key_len | ref   | rows | Extra                       |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | phppos_categories | ref  | phppos_categories_ibfk_1,parent_id | phppos_categories_ibfk_1 | 5       | const |    2 | Using where; Using filesort |
+----+-------------+-------------------+------+------------------------------------+--------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 0

Views: 144

Answers (2)

Rob MacMillian
Rob MacMillian

Reputation: 576

You are seeing 'using filesort' because you are ordering by the name column which is a varchar(255) field. 

ORDER BY `name` asc;

Explain reports that the number of records examined is only 2, and the index on the parent_id is being used. There are 2 rows in your result set. Therefore, MySQL did not do any extra or unnecessary work.

Personally, I would not worry about trying to get rid of the 'using filesort' in this case. Even if you had 300 rows in the table, you are still limiting the result set with where on an indexed field (parent_id), and the number of examined rows will equal the number in your result set.

Upvotes: 0

Trent Lloyd
Trent Lloyd

Reputation: 1892

You can potentially remove the filesort here by adding a multi-column index on (parent_id, name).

ALTER TABLE phppos_categories ADD INDEX (parent_id,name);

Generally speaking MySQL will only use a single index when it has to potentially sort with one of them (if you are simply using two indexes to query the table, it MAY use two indexes using index-merge but often that is not the case anyway). The solution is to create a single index covering all of the columns you need to query.

Second to this, MySQL can only do a "range" search or "sort" with the last column in the index that it uses. Any columns before that must be an exact equality match.

On that basis we can create an index with parent_id first, which has an exact equality match (=5) and then on name which is your order constraint.

Main thing to be aware of is that you don't want to add more indexes than necessary, and an index to suit every single possible query may not be sensible, especially given the additional storage space and work required to keep the index up to date. As part of that comparison, you also need to consider how often the table is updated. If it is seldom updated then more indexes are potentially less of an issue.

See here for some more information: https://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

Upvotes: 2

Related Questions