Re: Optimizing Select count distinct statement

This may be a stupid comment, but here goes anyway ... If a high percentage (say > 30%) of rows in your table have field2 = 'something', then in all probability it's going to be quicker for it to do a full table scan than to work through the indexes. If you think about it, a table scan is pretty fast in terms of disk access, compared with reading the index for field2, then using that to retrieve the row to check field1. Then again, who knows ... :-} Peter Whittleton (whittle@ssax.com) btw is using vi something to be proud of? ---------- From: ilist To: informix-list Subject: Re: Optimizing Select count distinct statement Date: 14 February 1997 06:00 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. >If field2 is part of a multiple field index then run update statistics >high on that field. I have encountered this with 7.2. It ignores >a multiple field index when you use only one field. > >staccuc@riq.qc.ca wrote: >: Anyone has an idea on how to optimize this kind of statement > >: select count (distinct field1) >: from table1 >: where field2 = 'something' > >: There is indexes on both field1 and field2, but Informix Online 7.2 is >: not using them according to the explain plan. > >: Any idea ? > >: Thank you > >-- >And I still use vi......... >