What about secondary indexes? (Tutorial)
Sundial Services
>From: apj...@aol.com (Apjones)
>Subject: What about secondary indexes?
>Date: 12 Jul 1995 04:16:22 -0400
>Can someone explain the difference between a secondary index and a second
>primary index?
>I normally use multiple primary indexes and I can't see what the secondary
>index is needed for.
There can only be one primary index on a table; it represents the field(s)
which are marked with a "*" in the "Key" column. Each key-value, being the
aggregate of all the key-fields, must be unique.
No matter how many fields are indexed in the primary key, you only have one
such key, and as with all indexes, it is the combination of all the key-field
values, taken together, which forms the value that is indexed. Furthermore,
again as with all indexes, the index is useful only when the request is for
the field values in left-to-right order. (A key on "Last Name,First Name" is
effective for searching for "Last" or "Last,First," but not for "First" alone.)
Secondary indexes are used for rapid access to other fields or combinations of
fields, and they are essential for good performance. If an index exists,
queries will use it; otherwise they will search the entire table sequentially
(more or less).
A secondary index can be "maintained" or not. If "maintained," it keeps
itself up-to-date all the time; the table must have a primary key to use this.
If the index is not "maintained," it remains effective only until anyone makes
any change to the table, then it becomes marked out-of-date and is no longer
usable until rebuilt. This is useful when you're about to run a series of
queries on a non-keyed table or when you do not want to bear the continuous
overhead of maintaining the key during updates, inserts and deletes.
HTH,
-Mike Robinson