Was this helpful?
Key Design and Performance
Key design is a complex subject. For additional information on keys, see the chapter “Choosing Storage Structures and Secondary Indexes.”
Characteristics of Good Keys
Good keys have the following features:
Use columns referenced in the WHERE clauses and joins of your queries
Are unique
Always document reasons for maintaining non-unique keys.
All keyed storage structures can enforce unique keys. They are:
Short
Static
Non-nullable
Characteristics of Bad Keys
Bad keys have the following features:
Wide
Use wide keys with caution.
You get fewer rows per page.
Evaluating the hash function takes more time with wide keys.
A wide key deepens the index level of B-tree and ISAM logarithmically, with respect to key width. B-tree is the least affected table structure.
Consider using a surrogate key as an alternative.
Non-static
Updating the index can slow performance.
Non-uniform duplication
A mix of high and low duplication can cause inconsistent query performance.
Sequential
Sequential keys must be used with care.
ISAM tables can be lopsided and the overflow chains can cause concurrency problems.
Control sequential key problems with a frequent modify schedule.
Multi-Column Keys and Performance
Multi-column keys have special issues. If used improperly in your query, the key cannot be used and the search does a full-table scan.
Keep the following in mind:
Use the most unique and frequently used columns for the left member of a multi-column key.
Searches on B-tree and ISAM tables must use at least the leftmost part of a multi-column key in a query, or a full-table scan can result.
Searches on hash tables must use an exact match for the entire key in the query, or a full-table scan can result.
Optimizer statistics are approximated by adding the statistics of the columns making up a multi-column key.
Surrogate Keys and Performance
When you use a short surrogate or internal key to replace a bad key, or because there is no good key, consider the performance trade-offs. The set processing of data includes the overhead of deriving the key.
Surrogate key types include:
Natural
Universal (a social security number or zip code are examples)
Environmental
These are local to an organization, like an employee number.
Design artificial. These are:
Local to an application
Hard to remember
Hard for users to understand
Can be hidden from users
Last modified date: 04/03/2024