3

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.

  • 2
    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? – Kamil Gosciminski Nov 5 '14 at 10:43
  • @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 – GhostGambler Nov 5 '14 at 11:06
  • With which MySQL version did you try your examples? – GhostGambler Nov 5 '14 at 11:09
  • @GhostGambler mysql version 5.5.34 – jakubfk Nov 5 '14 at 13:01
  • @ConsiderMe SQL_NO_CACHE do not change anything. Those are just examples. I did variety of test, and result is always the same. – jakubfk Nov 5 '14 at 13:04
1

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

  • So are you saying it's a bug? – Pacerier Apr 8 '15 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.