Re: Basic INDEX and SELECT questions

"Steve" <steve640_at_aol.com> wrote:
> I have some basic index questions that maybe someone can help with. I
> am just trying to get a better understanding of how this works (without
> becoming a DBA). If I have a table with fields A, B, and C and it has
> lots of records. Field A is mostly the same value, field B has a mix of
> values and field C is almost unique in every record. Given that, here
> are my questions:
> 1) Is an index that is created in the order A, B, and C better than one
> created in the order C, B, and A?

Probably not, but it depends on your queries. If in most of your queries you use "where A=:1 and B=:2..." and make no useful mention of C, then the order A,B,C would probably be better than C,B,A. (Although it might be better to drop A from the index altogether). But without further info, I would go with C,B,A

> 2) When doing a SELECT, is the WHERE clause better being "A=x AND B=y
> AND C=z" or "C=z AND B=y AND A=x", or no difference?

No difference. And if all your queries are like this (A,B,and C all specified by equality), it wouldn't make much difference which ordering of these you used in the index definition, either.

> 3) If I had an index on that table of "A,B" and an index just on field
> "A", how does the system know which index to use and would having these
> 2 indexes actually hurt?

A,B can do pretty much anything A can do, so A is probably not beneficial. Having that unnecessary index will slow down inserts/updates/deletes.

>
> Thanks for any insight you can provide.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB