Re: query executes slowly in a table with 2m records

[Rick James]

Please do
EXPLAIN PARTITIONS SELECT ...
You will probably find it hitting all 12*16 partitions. If so, this is because of the expression WHERE MONTH(quoteDate) = 6 .

Can you turn it into
WHERE quoteDate >= '2010/06/01'
AND quoteDate < DATE_ADD('2010/06/01', INTERVAL 1 MONTH)
? If so, do the EXPLAIN and see if it drops to only 16 partitions.

I suspect PARTITION handler is smart enough to deal with
PARTITION BY LIST (MONTH(quoteDate))
but the optimizer is not smart enough for
WHERE MONTH(quoteDate) = 6

Can we see some more of the SELECTs that hit this table. I suspect there is a "better" way to partition it.