MySQL Forums
Forum List  »  Partitioning

Re: Partition Pruning Issue while using Day() function in Range
Posted by: Mattias Jonsson
Date: September 22, 2011 03:36AM

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.

Mattias Jonsson

Options: ReplyQuote

Written By
Re: Partition Pruning Issue while using Day() function in Range
September 22, 2011 03:36AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.