Compound Indexes 
Author Message
 Compound Indexes

Hey now,

This a general question about how the Optimizer in SQL 7 works when there
are compound indexes.

Say a given table has a compound index on columns a, b and c.  If a SELECT
is run with a WHERE clause against column b will the compound index be of
any assistance to the Optimizer, or would it be best to create an index on
column b alone?

TIA,
Doo

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group



Sat, 04 Jan 2003 03:00:00 GMT
 Compound Indexes

Indexes on multiple columns are called composite indexes.
You can think of a composite index like a phone book. The names in the phone
book are sorted on multiple 'keys', typically lastname, then firstname, then
middle maybe street or city.

If you know someone's lastname but not their first name, you may be able to
find them in the index, at least more quickly than searching the whole book.
If you know someone's last and first name, you're even better off.

But what if you only know someone's first name? The fact that the book is
sorted by first name doesn't help, because first name is not the primary
sort key. Even if only one person in the whole book had the first name you
were looking for, it wouldn't help.

So searching for a value in column b, if the  index is on (a,b,c) will not
be able to take advantage of the index.

HTH
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


Quote:
> Hey now,

> This a general question about how the Optimizer in SQL 7 works when there
> are compound indexes.

> Say a given table has a compound index on columns a, b and c.  If a SELECT
> is run with a WHERE clause against column b will the compound index be of
> any assistance to the Optimizer, or would it be best to create an index on
> column b alone?

> TIA,
> Doo

> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group



Sat, 04 Jan 2003 03:00:00 GMT
 Compound Indexes
That's exactly what I needed to know...and what my gut told me.  But it's
best to hear it from the horse's, umm author's, mouth <g>.

Thanks for the great explanation!

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group



Quote:
> Indexes on multiple columns are called composite indexes.
> You can think of a composite index like a phone book. The names in the
phone
> book are sorted on multiple 'keys', typically lastname, then firstname,
then
> middle maybe street or city.

> If you know someone's lastname but not their first name, you may be able
to
> find them in the index, at least more quickly than searching the whole
book.
> If you know someone's last and first name, you're even better off.

> But what if you only know someone's first name? The fact that the book is
> sorted by first name doesn't help, because first name is not the primary
> sort key. Even if only one person in the whole book had the first name you
> were looking for, it wouldn't help.

> So searching for a value in column b, if the  index is on (a,b,c) will not
> be able to take advantage of the index.

> HTH
> --
> Kalen Delaney
> MCSE, SQL Server MCT, MVP
> www.InsideSQLServer.com
> Feed Someone for Free Today:
>      www.TheHungerSite.com



> > Hey now,

> > This a general question about how the Optimizer in SQL 7 works when
there
> > are compound indexes.

> > Say a given table has a compound index on columns a, b and c.  If a
SELECT
> > is run with a WHERE clause against column b will the compound index be
of
> > any assistance to the Optimizer, or would it be best to create an index
on
> > column b alone?

> > TIA,
> > Doo

> > --
> > Doo
> > Senior Data Architect / DBA
> > PlanetJam Media Group



Sat, 04 Jan 2003 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Creating a Compound Index on a Huge table

2. Compound indexes as primary keys

3. Compound index question

4. Compound Indexes

5. AddIndex : Compound Index problem

6. Seek on Compound Index in Delphi?

7. ~~compound indexes on DBF tables

8. Compound Index definition prob

9. Compound indexes

10. Compound Index Field Order

11. question on compound indexes


 
Powered by phpBB® Forum Software