Hints based on the ORDER BY clause

The database drivers generate an index hint based on the ORDER BY clause, If these conditions are met:

• All items named in the ORDER BY clause must belong to the same table.
• All items must have the same direction. Do not use a mixture of ASC and DESC.
• Specify an index that matches the ORDER BY clause.

An index hint is generated for the best matching index. To determine the best matching index, you compare the ORDER BY clause with all indexes defined on the table.

A hint is generated for the index that has the highest number of matching fields, in the same order as the ORDER BY. You cannot use an index that has fewer fields than the ORDER BY, because the remaining ORDER BY fields still require an undesirable sort operation.

If multiple indexes have the same number of matching fields, the index with the lowest index number is used.