Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL 5.1 using filesort event when an index is present

Probably I'm missing some silly thing... Apparently MySQL 5.1 keeps doing a Filesort even when there is an index that matches exactly the column in the ORDER BY clause. To post it here, I've oversimplified the data model, but the issue is still happening:

Table definition:

CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`owner_id`),
  KEY `date_created` (`date_created`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `user_profile` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

My problem is that event a simple SELECT is showing "Using filesort":

explain select * from event order by date_created desc;

And the result for the query explain:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE      event   ALL NULL        NULL    NULL    NULL    6       Using filesort

Is there any way for this type of queries to use the index insteas of doing a filesort?

Thanks in advance to everybody.

like image 560
Xavier Avatar asked May 24 '11 18:05

Xavier


People also ask

How do I avoid using Filesort?

To avoid a filesort, you have to find a way to get what you want without running into any of those (many) documented conditions. Increase the size of the sort_buffer_size variable. Increase the size of the read_rnd_buffer_size variable.

How do I know if indexing is working in MySQL?

To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.

What is using Filesort in MySQL?

In MySQL, filesort is the catch-all algorithm for producing sorted results for ORDER-BY or GROUP-BY queries. MySQL has two algorithms for filesort, both the original and the modified algorithms are described in the user manual.

Does MySQL use index automatically?

Mostly we create index when creating table. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed automatically by MySQL. In addition, you can add indexes to the tables which has data.


1 Answers

Since your CREATE TABLE statement indicates that you have less than 10 rows (AUTO_INCREMENT=7) and using FORCE INDEX on my installation will make MySQL use the index, I'm guessing the optimizer thinks a table scan is faster (less random I/O) than an index scan (since you're selecting all columns, not just date_created). This is confirmed by the following:

mysql> explain select date_created from event order by date_created;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | event | index | NULL          | date_created | 9       | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

In the above case, the index scan is faster because only the indexed column needs to be returned.

The MySQL documentation has some cases where using an index is considered slower: http://dev.mysql.com/doc/refman/5.1/en/how-to-avoid-table-scan.html

like image 127
Chris Morgan Avatar answered Sep 28 '22 02:09

Chris Morgan