Re: Partition Pruning Issue while using Day() function in Range

[Mattias Jonsson]

Case 1:
1. DAY() is not optimized for range pruning, So that is the reason why it does not prune your first question (as you see TO_DAYS works, in 5.5 you can also user RANGE COLUMNS partitioning directly).

2. It says it uses the INDEX_SENDER key, so it does use a good index.

Case 2:
1. Please show the full table, a sample of inserts, the EXPLAIN query and the result, since I'm not sure how P_DAY_13,P_DAY_14 can be chosen for "time >= '2011-09-12' and time < '2011-09-13'"
2. You are correct that all TO_DAYS(event_time) == NULL is stored there (like for invalid dates like 2000-00-00 or 2000-02-31), you can decrease the effect of this by having the first partition P_NULL VALUES LESS THAN (0).
2. It also uses the IDX_SENDER here.

Since only TO_DAYS is optimized for range partition pruning, it 'knows' that it is monotonic increasing (with potentional NULLs), but there is no such optimization for DAY, which makes it only prune on equal.

Regards
Mattias Jonsson