--- "Suresh Thalamati (JIRA)"
<[email protected]> wrote:
> description of TIMESTAMPADD/TIMESTAMPDIFF seems to
> indicate that column name
> should not be put in these escape function because
> it will use index, I am not sure that is right ?
> my understanding is if you put the column name
> inside the function , Index will NOT be used.
>
> TIMESTAMPADD page: (in the end ..)
> Note that you should not put a datetime column
> inside a timestamp arithmetic function in WHERE
> clauses because the optimizer will use any index on
> the column.
>
> TIMESTAMPDIFF section: (in the end .,)
> Note that you should not put a datetime column
> inside a timestamp arithmetic function in WHERE
> clauses because the optimizer will use any index on
> the column.
>
>
> jack wrote in his patch submission:
> More examples:
> SELECT * FROM t WHERE {fn TIMESTAMPDIFF(
> SQL_TSI_DAY, CURRENT_DAY, promisedDate)} <= 1
> Selects all rows from t with promisedDate at most
> one day from now. (It also selects rows with a
> promisedDate in the past). Note that this is
> probably not the optimal way to express the query.
> If promisedDate is indexed the Derby optimizer will
> not use the index. Better is
> SELECT * FROM t WHERE promisedDate <= {fn
> TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_DAY)
>
Thanks for the feedback Suresh,
I've already tweaked this patch about 5 times, so this
time I want to make sure I get it right before I
submit a new patch. How would you suggest I rewrite
it so that it is accurate?
