I was at a customer, helping them to make their import/export run fast and after testing a few things we realized that only one query was the source of the problem.

The query looked like this :

SELECT id, [...]
FROM the_table
WHERE  the clause
ORDER BY id ASC;

After a few tests we discovered that this query took around 15 seconds to run on only around 1 000 000 rows.

Running explain show the following result (slightly modified for more readability)

+----+------+-----+------------+--------+-------+-----+--------+-----------+
| id | [...] | type | possible_keys | key | key_len | ref | rows | Extra |
+----+------+-----+------------+--------+-------+-----+--------+-----------+
| 1 | [...] | index | idx1, idx2 | PRIMARY | 8 | NULL | 1103921 | Using where |
+----+------+-----+------------+--------+-------+-----+--------+-----------+


The problem is that they already were the correct indices on this table but MySQL was not able to choose the correct one. I tried to enforce the use of the correct INDEX with USE INDEX( … ) in the query and it worked fine but I did not like this solution.

After that I noticed that the ORDER BY clause was useless since the field used to sort was the primari key id.
And even if this clause is removed, the result set will be sorted on the field ‘id’.

You can verify this with the simple SQL below :


CREATE TABLE table1 (
id tinyint(4) NOT NULL AUTO_INCREMENT,
field varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SELECT * FROM table1;


Which returns


+----+-------+
| id | field |
+----+-------+
|  1 | 4     |
|  2 | 3     |
+----+-------+


Which is correct.

So after removing the ORDER BY clause, EXPLAIN was more polite :


+----+---+----+------------+-----+-------+-----------+-----+-----------+
| id | [...] | type | possible_keys | key | key_len | ref | rows | Extra |
+----+---+----+------------+-----+-------+-----------+-----+-----------+
| 1 | [...] | ref | idx1, idx2 | idx2 | 8 | const, const | 2 | Using where |
+----+---+----+------------+-----+-------+-----------+-----+-----------+


And the query took a few milliseconds to run.

So next times you have slow queries, check your ORDER BY clause first and see of you can remove it or find a workaround.