Archive for March 5th, 2010
From Stack Overflow:
I have a table
productwith products and table
salewith 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 »