Re: Optimizing Select count distinct statement
In article <5dvkdv$4n8@news.gvsu.edu>, Barry KLecka
<kleckab@river.it.gvsu.edu> writes
>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.
Are you sure you are using the first field in a multiple field
index. Remember you use an index you must used the first field in
the index in the where clause.
PS I have found that with SE (can't remember the version - we have
> 10 sites running SE!) that even the following does not use the
index:-
create index idx1 on tab1(a,b,c,d);
update statistics;
set explain on;
select a,b,c,d,e,f,g,h
from tab1
where a=1 and b=2 and d=3
Surely since I and querying on a and b at these are the first few
fields on the index then the index should be used. PS table
has >10000 rows and query returns < 5 rows. Columns a and b are
fairly unique i.e. the combination gives < 100-200 rows.
I eventually did
create index idx on tab1(a,b,d);
update statistics;
set explain on;
select a,b,c,d,e,f,g,h
from tab1
where a=1 and b=2 and d=3
and this index was used.
>
>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.........
>
Every day - now got vi for dos so I can use it at home too!
--
David Williams