Archive for March 28th, 2011
Answering questions asked on the site.
I have a table which I will call
saleto protect the innocent:
Sale id product price amount date
I need to retrieve ultimate values of
datefor each product:SELECT product, MIN(price), MAX(price), MIN(amount), MAX(amount), MIN(date), MAX(date) FROM sale GROUP BY product
The query only returns about 100 records.
I have all these fields indexed (together with
product), but this still produces a full table scan over 3,000,000 records.
How do I speed up this query?
To retrieve the ultimate values of the fields, MySQL would just need to make a loose index scan over each index and take the max and min value of the field for each
However, the optimizer won’t do when multiple indexes are involved. Instead, it will revert to a full scan.
There is a workaround for it. Let’s create a sample table and see them: