mysql - 按 SQL 查询排序

Question

这是在线门户的产品表结构。
目前产品表有超过100万条记录。

我创建了状态和交付 ID 索引。
我在按 ORDER BY status DESC、delivery_id ASC 列出产品时遇到性能问题
完整查询:

SELECT product_name FROM product ORDER BY status DESC, delivery_id ASC LIMIT 10

如何改进此查询以加快执行速度？

Answer 1

如 ORDER BY Optimization 下所述:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

[ <em>deletia</em> ]

• You mix ASC and DESC:

  SELECT * FROM t1 ORDER BY <strong><em>key_part1</em></strong> DESC, <strong><em>key_part2</em></strong> ASC;