Mysql partition search must scan the first partition

2019-08-2122:12:23 Comment

Mysql partition search optimization

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.


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.


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: