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>


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 Montoya
Received on Wed Nov 18 1992 - 20:57:19 CET

Original text of this message