Re: Query performance: 16 secs vs 5+ hrs

In article <3458B44D.89733D05@oci.com>, Tom Shell <Tom_Shell@oci.com> writes > >I can run the following query against a table with one minor (!?!) >change and vary the return times from 5+ hours to <16 >secs: >select field1, field2, field3, field4, field5, field6, field7 from>"dba".table where field2 < "number" and field1 >= "date" >order by field1 >The query above finishes in 5+ hours, but by adding field2 to the order >by clause, I can reduce the run time to <16 secs: >select field1, field2, field3, field4, field5, field6, field7 from>"dba".table where field2 < "number" and field1 >= "date" >order by field1, field2 >The table has 3 indexes on: >field2, field1 >field1 >field7, field1, field2 >I do not understand what is happening here. Can anyone shed some light >on the difference between the scripts. Thanks > I suspect the first query uses the index on just field1 because f the order and using field1 in the where clause. This means that for each value of field1 found where field1 > "date" Informix get a list of rows with that value. It then goes through the list of rows and searches for ones where field2<"number". The second query uses the index on field1 and field2 and so goes straight to the rows which you want and then just has to sort them (as the index is "then wrong way around"). Try 1. going into dbaccess (or isql) 2. running first set explain on 3. Running both of the above queries. 4. Looking at the file called sqexplain.out which is produced. This will tell you which indexes are being used. -- David Williams