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: 11/28/2023