Which Columns shoud be indexed?

[deko]

I've heard it said that *any* column field that appears on the right side of
a WHERE clause (anywhere in your database) should be indexed.

But this would mean nearly every column field in my table would have an
index! seems like overkill to me.

Is there some other criteria to use as a guideline? Is it really that
dangerous to *not* index a field that occurs on the right side of a where
clause? Is it better to avoid indexing text fields (especially long ones)
and rather index long integer fields only? How many indexes are too many?

Thanks in advance for your thoughts on this!

 

[John Vinson]

I've heard it said that *any* column field that appears on the right side of
a WHERE clause (anywhere in your database) should be indexed.

To be more precise... and field which a) is frequently used for
searching and b) has a reasonably large number of potential values
should probably be indexed. The exact decision point is as much art as
science, and depends on the particular database, user expectations,
observed performance problems, and so on.

But this would mean nearly every column field in my table would have an
index! seems like overkill to me.

You routinely put criteria on EVERY field in your table? I'm not sure
what information your table contains, but (frex) I've very rarely had
any occasion to search for a person's middle initial, or the second
line of their address.

Is there some other criteria to use as a guideline? Is it really that
dangerous to *not* index a field that occurs on the right side of a where
clause? Is it better to avoid indexing text fields (especially long ones)
and rather index long integer fields only? How many indexes are too many?

You are limited to 32 indexes per table (some of these can be
multiple-field indexes such as lastname/firstname/middleinitial
however), so there *is* a limit. In addition, though indexing improves
search performance, it can make updating the table slower (since every
added or edited record will require updating multiple indexes). It
isn't "dangerous" to not index a field; the downside to having a
non-indexed field is that searching your table with a criterion on
that field (with no criteria on any indexed field) will require a full
table scan, which can be timeconsuming with large tables. If your
users are satisfied with search performance, you may not need any more
indexing than the default.

 

[deko]

That's exactly the info I needed. Thanks!

better to figure out how to index now, while creating the database, rather
than after it gets corrupted due to improper indexing...

 

[Tim Ferguson]

better to figure out how to index now, while creating the database,
rather than after it gets corrupted due to improper indexing...

I'm not aware of jet databases being prone to "corruption due to improper
indexing" -- is this something specific you have heard? I do know that the
old file-managers like dBase could have their indexes broken, but even then
the data files were intact. Ordinary indexes are strictly for performance
(i.e. part of the physical model) and should not have any effect at all on
the data integrity (i.e. the logical model).

Bear in mind, too, that everything John said applies to SORTing criteria as
well as WHERE and GROUPing ones.

And none of this applies to contraint-type indexes, such as PKs, FKs and
unique indexes...


All the best


Tim F

 

[deko]

sounds like indexing, then, is largely a trade off between how fast a field
is found and how fast it is updated.

As for *Unique* indexes -- in one case, I use a unique index to prevent
duplicates... is there any other reason to use a Unique index (in addition
to a Primary Key?)

 

[Tim Ferguson]

sounds like indexing, then, is largely a trade off between how fast a
field is found and how fast it is updated.

Yup: that's pretty much it.

As for *Unique* indexes -- in one case, I use a unique index to
prevent duplicates... is there any other reason to use a Unique index
(in addition to a Primary Key?)

Only to prevent duplicates! For example, you may have a unique EmployeeID
that is the PK for the table, but you would also want the SSN number to be
unique too even if it's not stable enough to be used as a PK itself. You
may want to catch data entry errors in a hospital database by putting a
unique key on the combination of (PatientID, OperationDate).

You are correct that it is not very common, but there are situations where
a non-primary unique key is required.

All the best


Tim F