How to increase last day count query performance

Question

I have a table Products with products and table Sales with all sale operations that was done on these products. These tables are connected by Sales.PRODUCT_ID column.

I would like to get 10 most often sold products today and what I did is this:

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

But performance of it is very slow. What can I do to increase performance of this particular query?

Answer 1

Get rid of the 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

Make an index on sale (date, ankieta_id) for this to work fast.

If it is possible that you have less then 10 products sold for the table, but you sill need to show 10, then do this:

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

Answer 2

You're using the sale.DATE >= DATE_SUB( NOW( ) , INTERVAL 1 DAY ) condition on the left join. I guess that's why it is slow.

I suggest:

• Moving the date condition into a WHERE clause
• Using JOIN instead of a JOIN
• Adding and index on Sales.DATE would also help narrowing the query

---

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