Tuning: Designing keys for queries

Fully keyed queries typically offer the best performance. You can design keys to ensure that every query is fully keyed, although it may not be practical in all instances. Running all queries fully keyed requires defining a large number of keys, which in turn can cause performance degradation when using the add, update, and delete operations. In general, the Service Manager System Administrator should discuss query performance with the Database Administrator and tune the system accordingly.

Points to consider when designing keys:

  • Design keys for the most used queries.
  • You can optionally force users to issue fully keyed queries by not allowing partially keyed and non-keyed queries.
  • Specify the fields that are most commonly used at the beginning of the key in the query.
  • Specify fields that have many possible values at the beginning of the key. A key with a Boolean field at the beginning is inefficient, unless the majority of your queries return only a small number of records. For example, flag=true in the probsummary table eliminates 90% of all records.
  • Do not use the same field in multiple keys. If you update a record and then change the value of that field, update all of the indexes that contain that key.
  • Do not define more than 25 keys for one file. The more keys defined for a file, the more time is required to add, update, and delete records in that file. Likewise, if you define too few keys, the operations run faster but the searches on the individual file run slower.
  • There is no specific rule of how many keys you should define for a file. Logical consideration of these facts and your specific work environment are the best decision-making factors.

Related topics

Nonkeyed queries
Partially keyed queries
Stored queries
True queries

Key type definitions
Troubleshooting: Database debug
Tuning: Fully keyed queries
Tuning: Improving query speed
Tuning: Key selection algorithms
Tuning: Number of fields in files