Overview | Criteria | Joins | Aggregations | Ordering | Retrievals

The principles of indexing an ORDER BY clause are very similar to the principles of optimizing an aggregation. If an Omnidex index contains all of the columns in an ORDER BY clause, then the index pointers can be retrieved. This can avoid the cost of a large sort.

Many applications do paging as part of their presentation. Paging means that you look at the first page, then the second page and so forth. Indexing can especially help in this situation. It is especially painful to sort a large collection of rows only to display the first page or the second page. Accessing them in sorted order using an index prevents this need to sort and can dramatically improve the performance of an application.

As with aggregations, sometimes an index is not the fastest approach to order data. Using an index for ordering requires scanning the index from the beginning to the end. If the criteria isolates only a small number of rows, it would be faster to simply retrieve the rows and order them directly, than to scan millions of entries in an index. Omnidex uses the same AGGREGATION_THRESHOLD discussed in the aggregations section to decide when to use an index for ordering.

The following article explains basic strategies for optimizing ordering.

Prev | Next

See also: