Index on DateTime Field
Joel had a 3-million-row table with a 'datetime year to second' column and an index on it, but queries filtering with month(), day() and year() functions were slow because wrapping the column in functions prevents the index being used. Chris Hall suggested a straight range test instead (date_time BETWEEN '2001-01-16 00:00:00' AND '2001-01-16 23:59:59'), showing a query plan confirming an index path. Andrew Hamm added that EXTEND(date_time, year to day) = EXTEND(?, year to day) is handy for single-day lookups and, after testing, confirmed the optimiser still used the index. Problem resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a table w/ approx. 3,000,000 rows. One of the columns is defined
as "date_time datetime year to second". I use this column to access
the records, so I added an index, but the query is still very slow.
My select uses the month(), day() and year() functions, e.g.
select * from msglog_hist where month(date_time) = 1 and
day(date_time) = 16 and year(date_time) = 2001
Is that why the index doesn't appear to be used?
I can't determine the syntax to reference the column directly.
Any help would be apreciated.
Thanks,
Joel
Sent via Deja.com
http://www.deja.com/
jwz1@my-deja.com wrote:
>
...
> My select uses the month(), day() and year() functions, e.g.
>
> select * from msglog_hist where month(date_time) = 1 and
> day(date_time) = 16 and year(date_time) = 2001>
...
select * from msglog_hist where
date_time between '2001-01-16 00:00:00'
and '2001-01-16 23:59:59'
--
**************************************************************
Chris Hall Email: Chris.Hall@OrbisUK.com
Orbis Tel: +44 208 742 1600
http://www.OrbisUK.com Fax: +44 208 742 2649
Chris Hall wrote in message <3A647FD2.F3685E21@OrbisUK.com>...
>jwz1@my-deja.com wrote:
>>
>...
>> My select uses the month(), day() and year() functions, e.g.
>>
>> select * from msglog_hist where month(date_time) = 1 and
>> day(date_time) = 16 and year(date_time) = 2001>>
>...
>
>select * from msglog_hist where> date_time between '2001-01-16 00:00:00'
> and '2001-01-16 23:59:59'
>
Or, use the EXTEND() function, and maybe it will use the index:
where extend(date_time, year to day) = extend("2001-01-16", year to date)
Or something like that - I'm not 100% of the syntax... check the manual, and
check your query path.
Andrew Hamm wrote:
>
> Chris Hall wrote in message <3A647FD2.F3685E21@OrbisUK.com>...
> >jwz1@my-deja.com wrote:
> >>
> >...
> >> My select uses the month(), day() and year() functions, e.g.
> >>
> >> select * from msglog_hist where month(date_time) = 1 and
> >> day(date_time) = 16 and year(date_time) = 2001> >>
> >...
> >
> >select * from msglog_hist where> > date_time between '2001-01-16 00:00:00'
> > and '2001-01-16 23:59:59'
> >
>
> Or, use the EXTEND() function, and maybe it will use the index:
>
> where extend(date_time, year to day) = extend("2001-01-16", year to date)
>
> Or something like that - I'm not 100% of the syntax... check the manual, and
> check your query path.
Why do you think that my query wouldn't use an index:
select count(*) from tjrnl where cr_date between '2000-11-01 00:00:00'
and '2000-12-01 00:00:00'
Estimated Cost: 8
Estimated # of Rows Returned: 1
1) openbet.tjrnl: INDEX PATH
(1) Index Keys: cr_date (Key-Only)
Lower Index Filter: openbet.tjrnl.cr_date >= datetime(2000-11-01
00:00:0
0) year to second
Upper Index Filter: openbet.tjrnl.cr_date <= datetime(2000-12-01
00:00:0
0) year to second
That's about as good as it gets, I think...
Chris
--
**************************************************************
Chris Hall Email: Chris.Hall@OrbisUK.com
Orbis Tel: +44 208 742 1600
http://www.OrbisUK.com Fax: +44 208 742 2649
Chris Hall wrote in message <3A656BD3.7B9ED036@OrbisUK.com>...
>Andrew Hamm wrote:
>>
>
>Why do you think that my query wouldn't use an index:
>
>select count(*) from tjrnl where cr_date between '2000-11-01 00:00:00'
>and '2000>-12-01 00:00:00'
>
I don't think that your query wouldn't use an index, I would expect it, and
your query path proves it. However I was also passing on to Joel knowledge
of the existence and usage of the EXTEND function, which in many programming
circumstances would be easier to use (explained below). Quite frankly, I
don't know if the optimiser is smart enough to use an index against the
exact query I showed, without first testing it. Maybe someone on this thread
could do that (he says with a grin)
Even if my query doesn't use the index (all right, I'll test it ........ woo
hooo! It works! Smart little fella that optimiser) the convenience of the
programming style is useful.
Now that I know it works, programming with it becomes simply:
(using a 4GL prepare declare sequence here - sorry about the weak names but
I need my first coffee of the morning)
<SAMPLE CODE>
let somestring = "select * from atable where extend(dt, year to day) =
extend(?, year to day)"
prepare s_something from somestring
declare c_something from s_something
....
let p_dt = calculate the target day to find
open c_something using p_dt
fetch c_something into ......
</SAMPLE CODE>
Or some rubbish like that. That's to find an explicit day. However I reckon
a lot or most activity against datetimes would be to find ranges so of
course your sample code is ideal for that.
Horses for courses. Knowing all the techniques helps you to pick the best
one for the situation, and I hope we've helped Joel to get there.