[kfc@vd.dk]

I have to large tables, A and B with the common columns c and d
I make a select statement

select * from A,B
where A.c=B.c and A.d=B.d;

Should my indexes on the two columns be common indexes or single indexes?

create index INDX1 on A(c);
create index INDX2 on A(d);
create index INDX3 on B(c);
create index INDX4 on B(d);

or

create index INDX5 on A(c,d);
create index INDX6 on B(c,d);

[shammat]

For the given example, use the second alternative and put that column to the front which gives a higher selectivity (i.e. which returns less rows when queried with a specific value)

[kfc@vd.dk]

I am not sure, that I understand. What do you mean when you say, that I should bring til columns to the front?

[shammat]

Originally Posted by kfc@vd.dk

I am not sure, that I understand. What do you mean when you say, that I should bring til columns to the front?

There is a difference between

create index INDX5 on A(c,d);

and

create index INDX5 on A(d,c);

the first column in the index definition should be that column, which reduces the number of rows more than the other.

If a select * from a where c = 5 returns 100 rows and a select * from a where d = 14 returns 50000 rows, then c should be the first column.