Re: Partitioning with range(to_days) issue

[Sarah Sproehnle]

Hi Jools,

Have you benchmarked the performance of your queries on the partitioned tables vs. non-partitioned tables? I have found great performance benefits of partitioning by range using to_days. I think your issue is that EXPLAIN is not correctly estimating the rows that would need to be read. This is likely because the optimizer doesn't have the statistics. If a full table scan (type: ALL) is chosen, then EXPLAIN reports the number of rows it *thinks* are in the table at the time. But if partition pruning is used, the optimizer doesn't report the number of rows it thinks is in each partition, it just simply reports the total number of rows in the table. This could be considered a bug, but currently it is a documented issue. If you were to run the query, the whole table would *not* be scanned.. only the necessary partitions would be scanned.

Also, be careful using "SELECT count(*)" for your tests if you're not doing SELECT count(*) in your real application. COUNT queries will often cause the optimizer to choose an index scan (since you only need a COUNT) as opposed to whatever would be needed in your "real" query.

Sarah Sproehnle
MySQL AB
www.mysql.com