Re: Optimizing Select count distinct statement

In article <3303ff41.4618551@news.riq.qc.ca>, staccuc@riq.qc.ca wrote: >On 13 Feb 1997 17:53:03 GMT, kleckab@river.it.gvsu.edu (Barry KLecka) >wrote: >No, field2 is not part of an index, but I ran the UPDATE STATISTIC on >each indexed field of that table. > >>: select count (distinct field1) >>: from table1 >>: where field2 = 'something' >> To optimize the above stmt (i.e to get INFORMIX to use the index), you simply have to have an index on field2. No other field besides field2 matters. Additionally, field2 MUST be at the head (the first column) of the index if it is a composite index. Incidentally, you do NOT need to UPDATE STATISTICS (for this statement, at least). It will help if there are alternative paths to the information (e.g. multiple columns in the where clause) And, yes, vi is king. HTH. ---------------------- Rudy Fernandes GIC, Kuwait ----------------------