带order by的子查询的慢速sql查询

iszxjhcz 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(405)

问题是：

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
---------
id
product_id
list_price
created_at

输出：http://prntscr.com/kqh7fg
它显示了每个列表和大小的最低价格。

sql mysql sqlperformance sql-order-by

来源：https://stackoverflow.com/questions/52165078/slow-sql-query-with-sub-query-with-order-by

2条答案

按热度按时间

kninwzqo1#

因为你的子查询实际上什么都不做，所以试试这个。如果这不起作用，请发布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

赞(0）回复(0）举报 2021-06-20

1l5u6lss2#

我能解决这个问题。

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秒提高到0.5秒
谢谢！

赞(0）回复(0）举报 2021-06-20

我来回答

带order by的子查询的慢速sql查询

2条答案

相关问题

热门标签

最新问答