there are 2 tables (A, B). Inside A, there are 4 columns (a1, a2, a3, a4) and inside B, the columns are (b1, b2, b3, b4). And I got a query like the one below :

select a1, a2, a3, a4, b1, b2, b3, b4
from A, B
where A.a1 = B.b1
and A.a2 = B.b2
and A.a3 = 'abc'

What kind of index I should create in A & B ?

thx!!!

Hi,
You should create a composite index.

First question: what are the Primary Keys of A and B? It looks like the Primary Key of B might be (b1,b2). For every primary key, there is always a corresponding index anyway.

Second question: is there a Foreign Key between A and B? Again, it appears that there may be and it is (a1,a2) references B(b1,b2) - though this might not be the case. If there is a Foreign Key then in 99% of cases there SHOULD be (may not be) a corresponding index, i.e. (a1,a2) on A.

Based purely on what can be seen here, the ideal indexes would appear to be: (a3) on A and (b1,b2) on B. However, it depends on other factors such as the "selectivity" of A.a3, and the number of rows in the tables, as to whether such indexes would be useful - the optimiser may choose to ignore them and do full table scans.

As a rule of thumb for beginners I would say:
1) Declare a Primary Key (with its implict index) on every table
2) Declare Foreign Keys between tables for referential integrity
3) Create matching indexes for each and every foreign key unless you know a good reason not to.
That will take care of 95% of your index requirements.