I made few queries on my db lately
First query:
SELECT `table`.*
FROM (`table`)
WHERE table.id in(27172172,[...bunch of ids...],27171770)
ORDER BY field (`table`.`id`, 27172172,27172168,[...bunch of ids...],27171770);
[...result...]
25 rows in set (0.22 sec)
explain:
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | table | range | PRIMARY | PRIMARY | 4 | NULL | 25 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+
Second query:
SELECT * FROM (SELECT `table`.*
FROM (`table`)
WHERE table.id in(27172172,27172168,[...bunch of ids...],27171770)
) as x
ORDER BY field (`id`,27172172,27172168,[...bunch of ids...],27171770);
[...result...]
25 rows in set (0.00 sec)
explain:
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 25 | Using filesort |
| 2 | DERIVED | table | range | PRIMARY | PRIMARY | 4 | NULL | 25 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------+
I can clearly see that more complicated query is much faster than simple one. Select with "in" but without order runs in 0.00 sec. This phenomenon is visible not only in "order by field" queries but in almost all queries more complex than "select * from table where id=N" There is an article on percona.com (http://www.percona.com/blog/2010/03/18/when-the-subselect-runs-faster/) which describes similar situation, but this explanation does not precisely fit in here. So my questions are simple:
1) why query with subquery is more efficient than single query?
2) why mysql optimizer can't do such optimalization on it's own.
3) Where is my mistake? Wrong query? Wrong mysql configuration? Or maybe this is perfectly expected and "subquery trick" is world wide used?
Extra info: Table is innodb, current rows count is 26205445, so it is quite big. Id is primary key in this table. Mysql: Ver 5.5.34-32.0-log for Linux on x86_64 (Percona Server (GPL), Release 32.0) Server: debian based, ssd disks, 128GB ram.
