1. Registered User
Join Date
Nov 2002
Posts
16

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!!!

2. Registered User
Join Date
Jan 2003
Location
Woking
Posts
107

## Re: create index

Originally posted by ernestso
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.

3. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: create index

Originally posted by ernestso
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!!!
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•