hi,
I have a sql on a table like below

select field1, field2 from table1
where field3 = ?
and filed4 = ?

I have just created a composite index on the filed3&field4

Will that do? or should i add the fields in select clause also to this?

Thanks

I don´t understand the question. You don't need any index to do that query, you can create an index to make it quiker or for other proposes. What is your problem?

My question is, how to pick the columns for the index and on which order?

for instance, in the below query-
select
from a, b, c
where a.a1 = b.b1
and b.b1 = c.c1
and c.c2 = "AB"

so, in this case, which is the efficient way of indexing, shoul i go for composite indices?

The first column in a composite index should be the one you use most frequently.

For example you often join the table with c1 but you dont have c2 in the where condition all the time:
CREATE INDEX idx_c_c1_c2 ON c (c1, c2);

But I would prefer two Indexes:
CREATE INDEX idx_c_c1 ON c (c1);
CREATE INDEX idx_c_c2 ON c (c2);

Thanks for the info!
I just have another doubt on ordering the columns for my index.

For example, i have a join as below
WHERE c1.c = c2.c
AND c1.d = ?

In table c1, field c has the most distinct count when compared to field d.
so the index should be like idx_c1(c,d).

But since my query gets the field 'd' value as i/p, will changing the order as (d,c) helps me.

Thanks

Is your table so big that this is important?

Check the time your query runs without an index, create one, check again, create another one and check again.

Thats a good apprach!! My table is huge than you think, it has millions, thats why i'm concentrating more on this.

Have you tried SET EXPLAIN ON in isql, I tried that but i couldn't find the out file, i guess it should go to the current directory, but it doesn't.

Could you please help me on this?

Many Thanks!!!

Try "set explain file to '/tmp/explain';"

Are there a lot of different querys on this table or only one or two?

"set explain file to '/tmp/explain';" fails!!!
I'm getting syntax error.

I'm using isql version -7.30.HC6

Are there a lot of different querys on this table or only one or two?

YES, there are different query's with differenct WHERE criteria's.

Cheers!

Okay, I use DBAccess with IDS 11.50.UC6DE.

Take a look in the INFORMIXDIR, I remind that there was something but its also possible that I was there when I started dbaccess.

When there are a lot of different kind of querys, I would not create special composite indices but indices on single columns.

If a lot of querys use the same columns in the where condition then you can create a composite on it.

With IDS 11.50 a composite index can be used better.