Mysql query performance 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     | &lt;derived2&gt; | 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?
The order by column if have index gets highest priority when engines has to decide which index to use.

Hope it helps