Re: SQL: Using index in select for performance - question?

Shimon Gonen wrote: > > I am using informix online 7.1 > Have a table with 3 columns > 30,000 rows. > Have index on trip_level and date (see below) > > if I do a select ... where trip_level = 1 and > time >= "1996-08-21 09:34:07"; > or > select ... where time >= "1996-08-21 09:34:07" and > trip_level = 1; > it takes 17 seconds. > > if I do a select ... where time >= "1996-08-21 09:34:07"; > it takes 1 second. > > It looks like the server only uses the time index in the last case. > How can I get it to use the time index for all cases. > > Table info: > > Column name Type Nulls > > serial_number integer no > trip_level smallint no > time datetime year to second no > > > > Index name Owner Type Cluster Column > > 141_224 informix unique No serial_number > > ix148_5 informix dupls No trip_level > > ix148_6 informix dupls No time > The first two queries are using the trip_level index, since it is normally much faster to do an equality search (trip_level = 1) rather than a range search (time between "1996-08-21 09:34:07" and <<INFINITY>>). Adding to this problem, is the probability that the trip_level index is nowhere near as selective as the time index. If there are only a few discrete values for trip_level, the index structure is grossly inefficient, but the optimizer will prefer it due to the equality search. If your queries will always use trip_level in conjunction with time, try a composite index, rather than the two distinct indices. You also might want to try keeping the datetime as two separate components (possibly date, and datetime hour to second), and have queries attempt an equality search on date, if you can avoid range searching. Hope this helps. Paul. Paul Kapp ----> PLKapp@aol.com -OR- plk@interactive.visa.com