带有Order By的子查询的慢速SQL查询
带有Order By的子查询的慢速SQL查询
提问于 2018-09-04 19:15:41
下面是查询：
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。使用Order by减慢了子查询的速度。我已经创建了索引，但仍然需要5-6秒。
表结构：
目录:这是主产品目录表
Catalog
-------
id
sku
name
description产品:商店产品变体(大小选项)
Products
--------
id
catalog_id
size listing table:：stores产品列表，一个产品大小选项可以有多个不同价格的产品列表。
listing
---------
id
product_id
list_price
created_at输出：http://prntscr.com/kqh7fg
它显示了每个列表和大小的最低价格。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-09-11 16:24:18
我能够解决这个问题。
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我已经使用min (价格)和Group By Size来获得最低价格列表。
查询性能从5秒提高到.5秒
谢谢!
Stack Overflow用户
发布于 2018-09-04 22:46:44
由于您的子查询实际上并不执行任何操作，因此尝试执行以下操作。如果这不起作用，请发布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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52165078
复制相关文章
相似问题
腾讯云开发者
