慢速 SQL 查询与子查询与排序依据

Question

下面是查询：

SELECT product.catalog_product_id AS catalog_id, listing.id AS listing_id, 
product.size AS size, 0 AS amount, 
listing.list_price AS price, 
listing.created_at AS created_date 
FROM product
INNER JOIN listing ON listing.product_id = product.id 
WHERE product.catalog_product_id = XXXX
AND listing.id = (
SELECT l.id
FROM listing l
INNER JOIN product i ON l.product_id = i.id
WHERE i.size = product.size AND i.catalog_product_id = XXXX
ORDER BY l.list_price ASC, l.created_at ASC
LIMIT 1
) 

子查询是这个以最低价格获取列表 ID。子查询因排序方式而变慢。我已经创建了索引，仍然需要 5-6 秒。

表结构：

目录：这是主产品目录表

Catalog
-------
id
sku
name
description

产品 ： 存储产品变型(尺寸选项(

Products
--------
id
catalog_id
size 

列表表：：商店产品列表，一个产品尺寸选项可以有多个不同价格的产品列表。

listing
---------
id
product_id
list_price
created_at

输出： http://prntscr.com/kqh7fg

它显示了每个列表和尺寸的最低价格。

Answer 1

由于您的子查询实际上没有任何作用，请尝试此操作。如果这不起作用，请发布 explain 语句的输出，并避免使用"l"和"i"等表别名。它真的很难阅读，出错的机会也增加了很多(例如，由于某种原因，您本身加入了"product.size"(。

SELECT
product.catalog_product_id AS catalog_id,
listing.id AS listing_id, 
product.size AS size, 0 AS amount, 
listing.list_price AS price, 
listing.created_at AS created_date 
FROM        product
INNER JOIN  listing
ON      listing.product_id = product.id 
WHERE       product.catalog_product_id = ?
ORDER BY    listing.list_price ASC,
listing.created_at ASC
LIMIT       1

Answer 2

我能够解决这个问题。

SELECT product.catalog_product_id AS catalog_id, listing.id AS listing_id, 
product.size AS size,
listing.list_price AS price, 
listing.created_at AS created_date
FROM product
INNER JOIN listing ON listing.product_id = product.id
AND product.catalog_product_id = XXXX 
AND listing.id IN 
(
SELECT MIN(l.id)
FROM listing l
INNER JOIN product i ON l.product_id = i.id
WHERE i.catalog_product_id = XXXX 
AND list_price = (
SELECT MIN(list_price)
FROM listing l
INNER JOIN product it ON l.product_id = it.id
WHERE it.catalog_product_id = XXXX AND it.size = i.size
)
GROUP BY i.size
)

而不是使用：

ORDER BY l.list_price ASC, l.created_at ASC  LIMIT 1

我使用最小(价格(和按大小分组来获取最小价格列表。

查询性能从 5 秒提高到 .5 秒

谢谢！