Thread: Queries not always using index on timestamp search
Here are two queries (under 7.4.1): 'mytime' is a timestamp field that is indexed. select * from mytable where mytime > '2004-06-21' select * from mytable where mytime > current_date-3 Looking at an explain on these queries, the first one will use the index and the second one will not, even though 'current_date-3' produces the same date as the hard-coded one in the first search. Is there a way to get the second query to use the index? -- Mike Nolan
Mike Nolan wrote: > Here are two queries (under 7.4.1): > > 'mytime' is a timestamp field that is indexed. > > select * from mytable where mytime > '2004-06-21' > > select * from mytable where mytime > current_date-3 > > Looking at an explain on these queries, the first one will use the > index and the second one will not, even though 'current_date-3' > produces the same date as the hard-coded one in the first search. > > Is there a way to get the second query to use the index? Probably the simplest is to define a function ago(int) that does the calculation for you. Mark the function STABLE and that should do it. See the archives for more discussion of this. -- Richard Huxton Archonet Ltd