Kbase P159538: Are single-component indexes more efficient than composite indexes?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/4/2010 |
|
Status: Unverified
GOAL:
Are single-component indexes more efficient than composite indexes?
GOAL:
Is it better to index individual fields and allow the AVM to choose how to combine them, or to create a composite index?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
ABL has some extended index access strategies that can exploit combinations of single component indexes for very good performance. All other things being equal, creating indexes on individual fields will be more efficient. However, if a composite index narrows down the search much more than the two single-component indexes do, using the composite index can be much faster. This is particularly noticeable on large tables.
A trivial example would be finding the valedictorians in a database of all the students that ever attended a school. Selecting on a particular graduation year and class rank would return quite a few records that have to be matched up if each field was indexed separately, but if there was an index on ?graduation year, class rank? only one record would be returned.
The recommended way to design the index structure of an OpenEdge database is to start with single-component indexes, test performance with queries typical of the application, and consider a composite index if testing reveals poor performance with a particular combination of single-field indexes. Compiling the ABL code with the XREF option shows which indexes the AVM is using for a particular query, and how it is able to use them (bracket a certain section of the index, scan the whole index, and/or sort the result records).
If the USE-INDEX phrase is used. that phrase will override any of the rule-based optimizations that the AVM can do. In this case the performance that might be gained from having a particular index available may not be realized.