index when to add

[joemeshuggah]

hello all,

my understanding is that an index should be added to fields that are often
searched or sorted, and can be added to individual fields, or to a group of
fields.

if fields a, b, c, & d are typically sorted and used in joins, should i add
an index to field a, field b, field c, field d, fields a & b & c & d, fields
a & b & c, fields a & c & d etc etc?

is there a disadvantage to adding too many indexes?

what is the general rule as to whether or not an index should be added?

is there any harm in deleting an index after it has already been added?

thanks!

 

[John Spencer]

Question 1: Add an index for each field. Not for the combinations of the fields.

Question 2: Yes there is a disadvantage. Each index you add will slightly
slow down saving records as the index has to be updated in addition to the
record being saved. Also, there is some storage space used for the index.
And there is a limit on the number of indexes (32 maximum per table). Every
relationship will use one of the indexes for the table, but the index is
hidden from your view. So, you will usually not need to add indexes for
fields that are involved in a defined relationship.

Question 3: GUIDELINES ONLY. Add an index when you frequently sort or filter
based on the field. If the table is really small, an index may not be
helpful. If the data stored has little differentiation (e.g., True/false
field that has roughly half true and half false values) then an index may be
of little benefit.

If sorts and filters are slow and you don't have an index on the field - add
one and see if that helps.

Question 4: Deleting an index does not harm to your data. It may affect the
speed of sorts and filtering records. If it does, you can always add the
index back (assuming you have not reached the 32 index limit).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Access can only have 32 indexes per table, I believe. Therefore there is a
'too many indexes' limit.

Indexes are like little tables. Multiple indexes can take up more space than
the original table.

Indexes may speed up searches; however, they will slow down inserts,
updates, and maybe even deletions. Pick your poisen.

Using your example with all the indexes: When Access runs the query, it will
probably only pick one of the indexes to use and the others will just take up
space.

if fields a, b, c, & d are typically sorted and used in joins

If that is the norm, I'd create a composite index that contains all 4
fields. As an added bonus, it would also use such an index if you had
criteria just on A (but probably not on b, c, or d).

Joins are the biggest user of indexes especially if joining by the Primary
Key field. Criteria is the next biggest user of indexes. A distant third is
sorting.

If you have field(s) designated as a PK, it already has an index. In fact
Access has a bad habit of creating 2 indexes if your PK field name has ID;
key; code; or num in it such as PersonID. Delete the second such index.

As far as criteria, add an index based on that field and get out a
stopwatch. See if makes a noticable improvement. If not, drop the index. Of
course if you add 10K more records, it might change in the future.

If you really want to know what indexes are used by which queries, check out
Showplan:
http://articles.techrepublic.com.com/5100-10878_11-5064388.html

 

[TedMi]

If you relate one table to second table on field A, and to a third table on
field B, then you need separate indexes on A and B. You will need a compound
index on multiple fields only if multiple fields are involved in *one*
relation.

If you do need a compound index on fields A&B, then adding one on A alone is
wholly redundant. Example: think of a white pages phone book, which is
indexed on last name & first name. You don't need a separate index on last
name alone to find instances of "Smith".
-TedMi

 

[Paul Shapiro]

But don't create indexes on foreign keys if your relationships are enforced
(as they should be). Access automatically creates hidden indexes on foreign
keys, so if you create explicit indexes they will just be duplicates that
waste space and waste time being updated when data is edited.

Continuing TedMi's phonebook example, the index on {lastname, firstname}
will help a lot for a) sorting by lastname or sorting by lastname and then
firstname and b) finding someone by lastname and c) finding someone by
lastname and firstname, but cannot be used for sorting by firstname or
finding someone by firstname. For an index to be useful in sorting, the
columns have to be included in the index in the order they are used for
sorting. For an index to be useful in search, the search must at least
specify a criterion for the first column in the index.

The table's row count matters too. A table with a few thousand rows probably
won't see much if any performance improvement from indexes. Tables with
100,000 rows probably will. It might be worth identifying the specific
queries with performance issues and then considering indexes that could
improve those particular queries.