EXPLAIN EXTENDED

How to create fast database queries

Archive for March 5th, 2010

Aggregates and LEFT JOIN

Comments enabled. I *really* need your comment

From Stack Overflow:

I have a table product with products and table sale with all sale operations that were done on these products.

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

SELECT  p.*, COUNT(s.id) AS sumsell
FROM    product p
LEFT JOIN
        sale s
ON      s.product_id = p.id
        AND s.dt >= '2010-01-01'
        AND s.dt < '2010-01-02'
GROUP BY
        p.id
ORDER BY
        sumsell DESC
LIMIT 10

, but performance of it is very slow.

What can I do to increase performance of this particular query?

The query involves a LEFT JOIN which in MySQL world means that products will be made leading in the query. Each record of product will be taken and checked against sale table to find out the number of matching records. If no matching records are found, 0 is returned.

Let's create the sample tables:
Read the rest of this entry »

Written by Quassnoi

March 5th, 2010 at 11:00 pm

Posted in MySQL