Indexes on multiple fields -- redundant to reindex?

[Bruce Rusk]

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

 

[Allen Browne]

Don't index the *first* field in a multi-field index: Access can use the
multifield on that, so it would be redundant.

DO index any other fields in that index that need indexing. That's not
redundant.

As an example, consider a phone book that is indexed by Surname and First
Name. You don't need another phone book indexed by Surname: the compund
index can give you that. However, if you want to find a person by First
Name, you cannot use the standard phone book, because the First Names are
scattered after all the surnames. You therefore DO need another index to
search by First Name.

 

[Bruce Rusk]

Thanks for your reply, but this isn't quite what I was asking.

I understand that if the index is on two fields, eg. LastName and FirstName,
it would be redundant to create a new index on LastName (but possibly useful
to create a separate index on FirstName).

My question is: if I have an index on *three* fields, e.g. LastName,
FirstName and MiddleName, is it:

a) redundant to create an index on the two field LastName and FirstName

b) redundant to index LastName separately (as it would be given a two-field
index).

I haven't found any documentation on this point.

 

[Allen Browne]

Reply embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My question is: if I have an index on *three* fields, e.g. LastName,
FirstName and MiddleName, is it:

a) redundant to create an index on the two field LastName and FirstName

Yes: The index on LastName + FirstName + MiddleName is able to do all the
work that a LastName + FirstName index could do.

Extend the physical phone book example in your thinking and it will make
sense. Sorting by LastName, and then by FirstName, and then by MiddleName
incorporates the ability to sort by Lastname and FirstName.

b) redundant to index LastName separately (as it would be given a
two-field index).

Yes: for the same reasons. Think about the phone book example, and it will
make sense.

 

[John Vinson]

My question is: if I have an index on *three* fields, e.g. LastName,
FirstName and MiddleName, is it:

a) redundant to create an index on the two field LastName and FirstName
Yes.

b) redundant to index LastName separately (as it would be given a two-field
index).

Yes.

If those are the three searches you want to do, you need only the one
Index.

John W. Vinson[MVP]

 

[Bruce Rusk]

Thanks to you and John. That answers my question (and keeps my mdb
smaller!).