CODEWITHSUNDEEP
mysqlpartitioning
Jakanapes
Jakanapes

Reputation: 75

MySQL partition pruning always includes first partition in inequality query

I have a database partitioned by range on to_days(created_at).

The partitions are monthly (p1 - p50) with a pmax catchall on the end. In the below example, I'm expecting only partition p45 to be hit.

when I do an explain partitions select * from units where created_at > "2013-01-01 00:00:00" and NOW()

I get p1,p45 listed under the partitions column

This happens in both 5.1 and 5.5

Why is the optimizer including the first partition for an inequality check?

Upvotes: 2

Views: 598

Answers (1)

Antony Nguyen
Antony Nguyen

Reputation: 179

You asked this a long time ago, but I also ran into this issue and found a workaround here:

http://datacharmer.blogspot.com/2010/05/two-quick-performance-tips-with-mysql.html

... basically you should create a first partition that contains values less than (0), which will always be empty. The MySQL query optimizer will still include this first partition, but at the least it shouldn't be doing any resource-intensive scanning.

UPDATE: Here's a short summary of the URL linked in my original answer:

The official MySQL bugtracker acknowledges this behavior as a feature:

Bug Description:

Regardless of the range in the BETWEEN clause a table partitioned by RANGE using TO_DAYS function always includes the first partition in the table when pruning.

Response:

This is not a bug, since TO_DAYS() returns NULL for invalid dates, it needs to scan the first partition as well (since that holds all NULL values) for ranges.

...

A performance workaround is to create a specific partition to hold all NULL values (like '... LESS THAN (0)'), which also would catch all bad dates.

Upvotes: 5

Related Questions