RE: Index on DateTime Field
I know you've already checked, but you didn't mention it so....
Did you update statistics? If the optimizer thinks the table is empty it
may choose sequential scan.
cheers
j.
> -----Original Message-----
> From: Thomas Parsli [mailto:thomas.parsli@startsiden.no]
> Sent: Tuesday, January 16, 2001 12:02 PM
> To: informix-list@iiug.org
> Subject: Re: Index on DateTime Field
>
>
> jwz1@my-deja.com writes:
>
> > 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.
>
> Run your queries from ie. dbaccess with a "SET EXPLAIN ON;" first.
> Take a look at sqexplain.out -it should say
>
> 1) informix.msglog_hist: INDEX PATH
> not
> 1) informix.msglog_hist: SEQUENTIAL SCAN
>
> And _maybe_ this is faster?
> "WHERE date_time = DATETIME(2001-01-16) YEAR TO DAY"
>
> Thomas
>