index question

Hi everybody: I execute in a regular way a query like this: select distinct A from T where B='string' where de field A is an integer and field B is a char(8). The table is about 200.000 records length. Ther are about 5 or 6 distinct on fieldB, and about 20 distinct values on fieldA. What index should I create to improve the perfomance? Another question, after creating an index is necessary to execute update statistics for the table? Thanks in advance

> I execute in a regular way a query like this: > > select distinct A from T where B='string'> > where de field A is an integer and field B is a char(8). The >table is about 200.000 records length. Ther are about 5 or 6 distinct >on fieldB, and about 20 distinct values on fieldA. What index should I >create to improve the perfomance? Index on B should speed things up. Index on (B,A) would speed things even more as the engine only need to read the index pages. It really depends on how dynamic your table is. If you do a lot of updates/inserts/deletes, Index on (B,A) requires more overhead than index on B alone. > > Another question, after creating an index is necessary to >execute update statistics for the table? Yes. Bashar Chalabi

create index on field b (thats what your searching on). Yes you do needto update statistics. thanks Luis Ripoll wrote: > Hi everybody: > > I execute in a regular way a query like this: > > select distinct A from T where B='string'> > where de field A is an integer and field B is a char(8). The > table is about 200.000 records length. Ther are about 5 or 6 distinct > on fieldB, and about 20 distinct values on fieldA. What index should I > create to improve the perfomance? > > Another question, after creating an index is necessary to > execute update statistics for the table? > > Thanks in advance > >

Bashar Chalabi wrote: > > I execute in a regular way a query like this: > > > > select distinct A from T where B='string'> > > > where de field A is an integer and field B is a char(8). The > >table is about 200.000 records length. Ther are about 5 or 6 distinct > >on fieldB, and about 20 distinct values on fieldA. What index should I > >create to improve the perfomance? > > Index on B should speed things up. Index on (B,A) would speed things even > more as the engine only need to read the index pages. It really depends on > how dynamic your table is. If you do a lot of updates/inserts/deletes, Index > on (B,A) requires more overhead than index on B alone. Hmmm, actually I think it would be the other way around. If you are doing a lot of inserts/deletes, having a highly duplicate index on only field B would be much slower than having a composite on (B,A) (assuming that A does not depend on B, and therefore you would get more uniqueness with (B,A) than just (B) ). Of course, you still don't have very good uniqueness, with only 20 values of A. You might still want to put a more distinct field into the index. > > Another question, after creating an index is necessary to > >execute update statistics for the table? > > Yes. I don't think so. If the index was created AFTER the table was loaded, the statistics should be up to date. If the index was created BEFORE the table was loaded, then yes. June -- june_t@hotmail.com Back in Palo Alto, living on Kit Kat bars and chocolate chip cookies. Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.