Quote
id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |testpart |range |dateImpression |dateImpression |8 |NULL |43402 |Using where; Using index
OK, this is fine. It's using RANGE type and only scans 43402 lines.
Well, not quite. It also says "Using index". This means it is scanning the index without touching the data. "EXPLAIN SELECT * ..." might give you a different explain plan.
Possible bug: Your "4 day scan" is really parts of 5 days. The ending datetime is midnight of the next day. This would have to check two partitions:
BETWEEN '2009-08-31 00:00:00' AND '2009-09-01 00:00:00'.
I'm not a fan of using Partitioning for performance. Your example does not show a use case where partitioning even promises to deliver better performance. (If you are using it to drop old rows, then Partitioning, is an excellent choice.)
Why do you need to scan 80K rows? Just to count them? If so, I strongly suggest you keep a summary table, indexed by DATE, that has counts, subtotals, etc for each date. Calculate yesterday's totals each morning. No more big scans. Use the Summary table(s) for your reports.