How about modifying it to ?
Note that you should avoid using a datetime column
inside a timestamp arithmetic function in WHERE
clauses if there is a index on the column because the
optimizer will not use any index on the column.
Thanks
-suresht.
Jeff Levitt wrote:
--- "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?
