MySQL Sorting Results takes a long time

Question

Lately I've been getting MySQL to hang on specific queries. I have a table with 500,000+ records. Here is the query being run:

SELECT * FROM items WHERE (itemlist_id = 115626) ORDER BY tableOrder DESC LIMIT 1

Here is the explain:

| 1 | SIMPLE | items | ALL | NULL | NULL | NULL | NULL | 587113 | Using where; Using filesort |

And here is the process_list entry:

| 252996 | root | localhost | itemdb | Query | 0 | Sorting result | SELECT * FROM items WHERE (itemlist_id = 115642) ORDER BY tableOrder DESC LIMIT 1 |

Any idea what could be causing this query to take 10 minutes to process? When I run it manually it's done quickly. (1 row in set (0.86 sec))

Thanks

Answer 1

You need to create an index on items (itemList_id, TableOrder) and rewrite the query a little:

SELECT  *
FROM    items
WHERE   itemlist_id = 115626
ORDER BY
        itemlist_id DESC, tableOrder DESC
LIMIT 1

The first condition in ORDER BY may seem to be redundant, but it helps MySQL to choose the correct plan (which does not sort).