Progress Programming Tips

PPT-20  DB Design - Indexing rules

Tip:

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 iterrative one, expect to identify indexing requirements, both for
and against, as you go.

The wording is mine but the original sources are many.

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 with the advent
of v7 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.

Conclusion -

With the multiple index resolution and the compressed indexes I
would now lean towards too many indexes rather than too few.  Not to
say that the perfect balance isn't still perfect mind you!

Remember that many of these issues only apply when v7+ 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.

For a thorough understanding of query resolution see the Engine
Crew Monograph at http://www.peg.com/monographs/.

My thanks to the Engine Crew for their continued, excellent
efforts with the DB and sharing technical knowledge!

Wisdom:

Progress is a powerful tool, get your safety glasses and start
building!

Rod Gaither                  | rdg@worldinfo.com
World Information Systems    | (910) 333-2580  Voice
Greensboro, NC               | (910) 333-2584  Fax