Indexes
Overview
- Only create indexes that will actually be used as retrieval keys
- Only create indexes for large tables (small tables may run
faster without indexes)
- For retrieval keys that are only occasionally or rarely needed,
create the index just before using the retrieval key, and then delete
the index immediately afterwards
Uses
- where clauses
- order by clauses
- group by clauses
- joins
- min(), max()
- Comparing columns with the =, >, >=, <, <=, or
between operators
- Comparing columns with the like operator, where the argument
is a constant string that doesn't start with a wildcard character
- Searching on column-name is null
- where clauses with multiple
and conditions; the
index
can only be used if every column in the where clause is in the index
Primary Index
- An index specified on a unique ordering field.
- An ordered file of records with two fields: the ordering key
field for each record in the table, and a pointer to the location of
the record.
- A table can have up to one primary index or up to one
clustering index, but not both.
Clustering Index
- An index specified on a non-unique ordering field.
- An ordered file of records with two fields: a field for each
distinct clustering field in the table, and a pointer to the location
of the first record with that value for its clustering field.
- A table can have up to one primary index or up to one
clustering index, but not both.
Secondary Index
- An index specified on any nonordering field, a key field or a
nonkey field..
- On a key field, the index is an ordered file of records with
two fields: the key field for each record in the table, and a pointer
to the location of the record.
- On a nonkey field, the index is an ordered file of records with
two fields: a field for each distinct nonkey field in the table, and a
pointer to the location of the first record with that value for its
nonkey field.
- A table can have more than one secondary index.
Multilevel Index