Administration: Omnidex Indexing

Indexing Concepts

Ordering

Omnidex indexing is also valuable for processing ORDER BY clauses. Omnidex first considers whether the ORDER BY clause requests data in ascending order or descending order.

Ascending Order

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 record pointers can be processed in sorted order. Omnidex supports this capability for ascending ORDER BY clauses. This can avoid the cost of a large sort of the data after it is retrieved from the database.

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

Descending Order

Omnidex indexing does not optimize descending ORDER BY clauses, but this feature is a planned enhancement.

When to Avoid Indexes

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.

Additional Resources

See also:

 
Back to top
admin/indexing/concepts/ordering.txt ยท Last modified: 2016/06/28 22:38 (external edit)