sql - 如何提高最后一天计数查询性能

Question

我有一个包含产品的表 Products 和一个包含对这些产品进行的所有销售操作的表 Sales。这些表由 Sales.PRODUCT_ID 列连接。

我想获得今天最常销售的 10 种产品，我所做的是:

SELECT product.* , COUNT( sale.ID ) SUMSELL
FROM Products product
LEFT JOIN 
   Sales sale 
   ON 
       sale.ANKIETA_ID = product.ID AND 
       sale.DATE >= DATE_SUB( NOW( ) , INTERVAL 1 DAY )
GROUP BY product.ID
ORDER BY SUMSELL DESC
LIMIT 0 , 10

但是它的性能很慢。我可以做些什么来提高这个特定查询的性能？

Answer 1

去掉LEFT JOIN:

SELECT  product.*, sumsell
FROM    (
        SELECT  ankieta_id, COUNT(*) AS sumsell
        FROM    sale
        WHERE   sale.DATE >= DATE_SUB( NOW( ) , INTERVAL 1 DAY )
        GROUP BY
                ankieta_id
        ) s
JOIN    product
ON      product.id = s.ankieta_id
ORDER BY
        SUMSELL DESC
LIMIT 10

在 sale (date, ankieta_id) 上建立索引以使其快速运行。

如果您在表格中售出的产品可能少于 10，但您仍需要显示 10，则执行以下操作:

SELECT  *
FROM    (
        SELECT  product.*, sumsell
        FROM    (
                SELECT  ankieta_id, COUNT(*) AS sumsell
                FROM    sale
                WHERE   sale.DATE >= DATE_SUB( NOW( ) , INTERVAL 1 DAY )
                GROUP BY
                        ankieta_id
                ) s
        JOIN    product
        ON      product.id = s.ankieta_id
        ORDER BY
                SUMSELL DESC
        LIMIT 10
        ) p
UNION ALL
SELECT  *
FROM    (
        SELECT  product_id, 0
        FROM    products p
        WHERE   products NOT IN
                (
                SELECT  *
                FROM    sale
                WHERE   ankieta_id = p.id
                        AND sale.DATE >= DATE_SUB( NOW( ) , INTERVAL 1 DAY )
                ) p2
        LIMIT 10
        ) p2
ORDER BY
        sumsell DESC
LIMIT 10