On Wed, 6 Jul 2005 16:00:49 -0700, "Bruce Rusk"
<br***************@stanford.edoo> wrote:
I have a question about indexing multiple fields and whether it's redundant
to index the individual fields separately.
As I understand it, if there is an index on two fields, say idxFullName
which includes LastName and FirstName, in that order, it would be redundant
to create a separate index on LastName since idxFullName would already be
indexing LastName. But it would be necessary to create a separate index for
FirstName to assist with searches on FirstName alone (e.g. for all "Bob"s or
"Mary"s regardless of surname).
My question is: does this apply to indexes that include three or more
fields?
For example, if I have a table with LastName, FirstName and MiddleName and
create an index on all three fields (in that order), and then do a search on
LastName = x and FirstName = y, will the Jet Engine be able to figure out
that it can use the first two fields of the index? Similarly, will this
three-field index also be helpful for searches covering just the LastName
field?
If anyone knows the answer (or can point me to someplace in the Access/Jet
documentation that I have overlooked), thanks in advance.
Bruce Rusk
If I have, say, a 3-field identifier of some kind, I'll try to make an index
for the combination, and I'll make indexes for any individual fields I think
are highly likely to be searched or sorted individually. I'll make the first
field of the multi-field index -not- be one of those because then the
multi-field index can at leas be some help in the less common case, though
it's bigger and therefore slower to search than the single-field indexes. Be
careful not to go overboard because more indexes adds to the time it takes to
do updates and increases the likelihood of multi-user lock conflicts. For
fields less commonly searched on, it may be better to use no index and live
with the table scan penalty for those.
I have some uncertainty about how clever Access can get in using indexes
because I know that SQL Server can be really damn clever. For instance, if
I'm searching on the second field in a multi-field index, SQL Server may still
use the index even though it has to do a full scan of it because the estimated
number of reads is still smaller that way than doing the full scan of the
table first. I don't know if Access/Jet does that kind of optimization or
not.