Mysql slow query: INNER JOIN + ORDER BY causes filesort
- by Alexander
Hello!
I'm trying to optimize this query:
SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags`
ON `posts`.id = `posts_tags`.post_id
WHERE (((`posts_tags`.tag_id = 1)))
ORDER BY posts.created_at DESC;
The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different indexes, no luck.
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`created_at` datetime default NULL,
PRIMARY KEY (`id`),
KEY `index_posts_on_created_at` (`created_at`),
KEY `for_tags` (`trashed`,`published`,`clan_private`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=44390 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `posts_tags` (
`id` int(11) NOT NULL auto_increment,
`post_id` int(11) default NULL,
`tag_id` int(11) default NULL,
`created_at` datetime default NULL,
`updated_at` datetime default NULL,
PRIMARY KEY (`id`),
KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63175 DEFAULT CHARSET=utf8
+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | posts_tags | index | index_post_id_and_tag_id | index_post_id_and_tag_id | 10 | NULL | 24159 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | posts | eq_ref | PRIMARY | PRIMARY | 4 | .posts_tags.post_id | 1 | |
+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
What kind of index I need to define to avoid mysql using filesort? Is it possible when order field is not in where clause?
