Kbase P26002: Why should I add an Index to my table?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
25/07/2007 |
|
Status: Verified
GOAL:
Why should I add an Index to my table?
GOAL:
Reason of adding an index to a table
FIX:
Here are the simple rules to live by when defining indexes for your database.
If one of these rules is not the reason for creating an index then further examination is in order. It is important to understand that defining indexes is a tradeoff when it comes to performance. Having too many is bad, having too few or the wrong ones is bad, only having just the right number and right ones is good. To find this balance you need to know the rules below, expected table counts, and the way in which the application will use the tables! Not all of this is possible when first sitting down at the design table. That is why the development process in depicted as an iterative one, expect to identify indexing requirements, both for and against, as you go.
1. Fast, direct record access
2. Sorting
3. Support for relations (really a variation of 1)
4. Enforcing uniqueness
A new issue has surfaced with index definitions since advent of v7 and higher and multiple index resolution. In addition to whether an index is needed or not you also have to decide between several, single component indexes and a single multiple component index or even both.
As with the basic decision to index or not the choice between multi-component and single component is based on the intended usage. Below are some VERY general guidelines to follow for making the single/multiple index selection.
1. Unrelated fields used in conjunction for lookups should be candidates for individual indexes. An example being last name and age for a person record.
2. Use a multiple component index for any simple case of 2-4 above. As examples - Primary key is made up of two fields, Brand and Part# are unique in combination, Sorting on the combination of State and City, etc.
3. In general, single component indexes provide for the most flexible use of lookup criteria, ie mixing and matching the fields used while getting the best server side query resolution.
With the multiple index resolution and the compressed indexes it would now lean towards too many indexes rather than too few.
Remember that many of these issues only apply when v7 and higher indexing is in use. Using the FIND statement or options like USE-INDEX force v6 style indexing behavior thus limiting the effectiveness of the above designs.