MySQL排序结果需要很长时间

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:

最近我一直在让MySQL挂起特定的查询。我有一张包含500,000多条记录的表格。这是运行的查询:

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

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 |

| 1 |简单|物品|所有| NULL | NULL | NULL | NULL | 587113 |用在哪里;使用filesort |

And here is the process_list entry:

这是process_list条目:

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

| 252996 |根| localhost | itemdb |查询| 0 |排序结果| 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))

知道什么可能导致此查询需要10分钟处理?当我手动运行它时,它很快就完成了。 (1排(0.86秒))

Thanks

1 个解决方案

#1

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

您需要在项目(itemList_id,TableOrder)上创建索引并稍微重写查询:

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).

ORDER BY中的第一个条件似乎是多余的,但它有助于MySQL选择正确的计划(不排序)。

#1