mysql slow select order by on a large table

Question

I have a large table stored in mysql (basically, a log) that looks like this: ``` CREATE TABLE `log` ( `ID_1` int(10) unsigned NOT NULL, `ID_2` int(10) unsigned NOT NULL, `DELTA` tinyint(3) unsigned NOT NULL, `ACTIVE` tinyint(1) NOT NULL, `DATA` bigint(20) unsigned NOT NULL, ) ENGINE=MyISAM DEFAULT CHARSET=utf8$$ ``` No index. Data is inserted at random moments with random data and it work fine. Then, the table is taken offline (no read/write operations on it). The table size in this moment is around 180M records. I am adding an index on ID_1, ID_2, DELTA, ACTIVE fields (all 4, ascending). It works fairly fast (3-4 minutes). Now, I am trying to get all the data from the table in ascending order by ID_1, ID_2, DELTA, ACTIVE (same fields and in the same order as in index) but the select stays for ages in 'SORTING RESULT' (in SHOW PROCESSLIST) until first row is returned. I tried to hint/force the SELECT statement to use the index (i.e. FORCE INDEX / USE INDEX) but there isn't any differ…

Accepted Answer

To efficiently sort the data, you must add a compound index using all the columns you want to use in the `ORDER BY` clause. The order of the columns in the index must be the same as the order of the columns in the `ORDER BY` clause.

In your case, it would be:

```
ALTER TABLE `log` ADD INDEX mySortIndex(`ID_1`, `ID_2`, `DELTA`, `ACTIVE`);
```

To check how MySQL is executing your query, use:

```
EXPLAIN SELECT `ID_1`, `ID_2`, `DELTA`, `ACTIVE` FROM `log` ORDER BY `ID_1`, `ID_2`, `DELTA`, `ACTIVE` ASC;
```

The output will tell you which indexes are used.

If you are only going to need a known number of result rows, you may use `LIMIT` to prevent MySQL from sorting the entire table:

```
SELECT `ID_1`, `ID_2`, `DELTA`, `ACTIVE`
FROM `log` ORDER BY `ID_1`, `ID_2`, `DELTA`, `ACTIVE` ASC
LIMIT 100;
```

mysql slow select order by on a large table

Solution

Problem