Re: query performance

Hi Henk, how can we say that it makes sense to use an index, if we do not know how many rows will be returned by the query ? Normally it makes sense if the query will return 10 percent or less of the total rows. Additionally I would recommend to detach the indexes from the tablespace and I think the composite index is useless unless you need the index for an ORDER BY, a GROUP BY or a direct lookup ( both columns should be compared by "=" operator ). After the detach you should try to alter the "lst_date" index to cluster. This will perform a physical reorganization of your data pages and the server can perform a wonderfull read ahead operation. The time it takes to return the rows depends on your disk speed and the size of your cache buffer. Henk J. Sanders wrote: > > Some people have already reacted, many thanks. > > To give some more information: > > SQLEXPLAIN shows that the index lst_date is taken. I think this will depend on the date you are looking for. > All other queries perform excellent, only this one is in trouble. > > To whom we do not really understand the problem: > > fst_date <= "1998-4-1" ...... > LST_date >= "1998-4-1" > > Example (by fst_date index) > > fst_date lst_date > ---------- ---------- > 1998-3-30 1998-4-1 > 1998-3-30 1998-4-2 > 1998-3-30 1998-4-3 > .... > ..... > 1999-4-1 > 1998-4-1 1998-4-1 > 1998-4-1 1998-4-2 > 1998-4-1 1998-4-2 > ..... > ...... > 1999-4-1 > 1998-4-2 > and so on. > > As you will see that an index will not be of much help. > The column lst_date was added to the index to avoid lots of duplicate > index entries: > about 200 a day. It's no problem if you have a lot of duplicates in your indexes. A trivial example: You have a column that contains two unique values, "Y"es and "N"o. 1 Million rows contain the value "Y", 10 rows contain the value "N". Informix would never use the index if you are looking for the value "Y"es ( except OPTCOMPIND is set to 0 ). But if you are always looking for the value "N"o, we would use the index. It's usefull to read between the lines when you read Informix's recommendations. > If more information is required please let me know. Bye Stefan Weideneder