MySQL: Why queries with subqueries are much faster than single query?

Question

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.

• Kamil Gosciminski: Why would you assume, that the second query is "more complicated"? It's actually equal. What is the time between your execution of those queries, and did you ran the "simple query" first? It seems like you're dealing with query result caching. Doesn't it?
• Ulrich Thomas Gabor: @ConsiderMe Caching of the first query will not affect the second query as the query cache compares SQL statement strings by character not by result set: dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
• Ulrich Thomas Gabor: With which MySQL version did you try your examples?
• jakubfk: @GhostGambler mysql version 5.5.34
• jakubfk: @ConsiderMe SQL_NO_CACHE do not change anything. Those are just examples. I did variety of test, and result is always the same.

Answer 1

I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.

• Pacerier: So are you saying it's a bug?