0

We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are the same, but the performance is quite interesting.

SELECT * FROM table WHERE column='97807dsad1' group by indexedColumn ORDER BY CreatedDate ASC;

SELECT * FROM (SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC) as thetable GROUP BY indexedColumn;

column has no index. CreatedDate has index. indexedColumn has index.

The first query takes 30 seconds, the second 2.26 seconds. I tested it a bit more and inner select:

SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC;

It is slow without the ORDER BY and quick with the ORDER BY.

May matter: the total results for the query is 4 rows (out of the 1.8 million records in the table)

The results of EXPLAIN for the first:

+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | table | index | NULL          | indexedColumn  | 202     | NULL | 1806555 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+

The results of EXPLAIN for the second:

+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |     108 | Using temporary; Using filesort |
|  2 | DERIVED     | table  | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+

The result of explain for only the inner select of the 2nd query:

+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+

What is the explanation for this?

1

The order by column if have index gets highest priority when engines has to decide which index to use.

Hope it helps

  • But i can't see how this would make my query quicker... It still has to filter out 4 records (that many maches in our example) out of 1.8 million. – Zsolt János Nov 7 '14 at 13:33
  • try using explain and this might give you better idea about the query plan – Nawaz Sohail Nov 7 '14 at 13:34
  • Added explain results, but that is not really explaining to me WHY exactly the inner select is quick. – Zsolt János Nov 7 '14 at 13:38
  • Please read out at dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html for further clarifications.. – Nawaz Sohail Nov 7 '14 at 13:46

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.