index column order perfomance effects
From: Devan F. dewey <dewey_at_centerline.com>
Date: 18 Nov 1992 19:57:19 GMT
Message-ID: <dewey-181192145202_at_sebastian.centerline.com>
Date: 18 Nov 1992 19:57:19 GMT
Message-ID: <dewey-181192145202_at_sebastian.centerline.com>
I have a view of my database which links three tables by COMPANY#. One of these tables contains a rep_code and another contains a followup_date.
There is an index on table1(rep_code).
When I create an index on table2(followup_date, company#) and did:
select count(*) from view
where rep_code = 'AA' and followup_date = TODAY;
the query took over an hour.
So I drops the index on table2 and created and index on the same columns in a different order: table2(company#, followup_date).
Now the same query executed in 8 seconds.
I'm happy I got it working, but can anyone explain the dynamics of why it works this way. I think I should know this before I create too many more indexes.
Thanks for any info.
-Devan
Devan F. Dewey | Senior Systems Analyst CenterLine Software | dewey_at_centerline.com 10 Fawcett Street | "Leme esplain - no dere is Cambridge, MA 02138 | too much. Leme sum up." -Inigo MontoyaReceived on Wed Nov 18 1992 - 20:57:19 CET