Re: After partition also select is scanning all the partitions - need help

[Rick James]

When timing a query, add SQL_NO_CACHE, run the query twice, and report the second run. This avoids the Query cache, which can immensely skew the timings, and lets the disk reads be cached otherwise.

Do not trust EXPLAINs estimates, only trust the "nnnn rows in set" at then end of the SELECT for how many rows it _returns_.

For the number of rows touched, trust "Rows examined" in the slowlog. The delta of 'Handler_read_next' is possibly the same value.

On another note, is there nothing in the 11pm hour? <= '2010-09-06 23:00:00'

There is a feature/bug wherein PARTITIONing often looks at the _first_ PARTITION. Does PART_20100817000000 have anything in it? A slight improvement would be to create a dummy first partition that is empty.