Re: I searched table on one partiton, but MySQL scanning two partitons

[Rick James]

Yes. Note that the extra one is always the first partition. Note what happens with invalid DATETIME strings:

mysql> SELECT TO_DAYS('2011-02-29'), TO_DAYS('2012-02-29');
+-----------------------+-----------------------+
| TO_DAYS('2011-02-29') | TO_DAYS('2012-02-29') |
+-----------------------+-----------------------+
|                  NULL |                734927 |
+-----------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)

NULLs (and zeros) go into the first parition (because of the LESS THAN clause).

The standard recommendation is to have nothing deliberately in the first partition. That is, start the partitions you really want with the second partition. That way, the cost of the seemingly spurious reach into the empty pmin will be minimal.