I need to select rows from 2 tables using SQL:

SELECT A.column1, A.column2, B.column1, B.column2 FROM table1 A join table2 B on A.column1 = B.column1 ORDER BY A.column1, B.column1

How should I create an index to make the selection faster?
Maybe I should create more than 1 indexes?

Thanks!

Bruce

For that query, there should be an index on a.column1 and another on b.column1. You "order by" is redundant, remove the b.column1 since it is the same as a.column1.

Andy

Thanks Andy.

Good point. Now suppose I change the ORDER BY to ORDER BY A.column1, B.column2

SELECT A.column1, A.column2, B.column1, B.column2 FROM table1 A join table2 B on A.column1 = B.column1 ORDER BY A.column1, B.column2

Then should I create 3 indexes?

1. on A.column1
2. on B.column1
3. on B.column2

Or should I create some sort of composite index to handle
"ORDER BY A.column1, B.column2"

Thanks.
Bruce

No, you still only need 2 indexes. Just make the one on B column1,column2.

Andy

Cool, Thanks Andy.

What would be the general rules of making indexes for query like this: (A, B, C are 3 tables)

SELECT ... FROM ... ORDER BY A.c1, B.c1, C.c1, A.c2, B.c2, C.c2

Thanks
Bruce

Adding indexes just to satisfy an order by clause may not be the best idea. You get the most bang for the buck with indexes when they cover foreign key relationships and the predicates in a where clause.

Andy