This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:concepts:ordering [2011/01/17 22:35] els |
admin:indexing:concepts:ordering [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration: Indexing Strategies ====== | + | ====== Administration: Omnidex Indexing ====== |
| ===== Indexing Concepts ===== | ===== Indexing Concepts ===== | ||
| Line 16: | Line 18: | ||
| ==== Ordering ==== | ==== Ordering ==== | ||
| - | 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. | + | 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. | ||
| - | 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. | + | === 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 [[admin:indexing:concepts:aggregations|aggregations section]] to decide when to use an index for ordering. | 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 [[admin:indexing:concepts:aggregations|aggregations section]] to decide when to use an index for ordering. | ||
| - | The [[admin:indexing:basic:orderby|following article]] explains basic strategies for optimizing ordering. | + | ==== ==== |
| + | The [[admin:indexing:strategies:orderby|following article]] explains basic strategies for optimizing ordering. | ||
| ===== ===== | ===== ===== | ||
| **[[admin:indexing:concepts:aggregations|Prev]]** | | **[[admin:indexing:concepts:aggregations|Prev]]** | | ||
| - | **[[admin:indexing:concepts:retrievals|Next]]** | + | **[[admin:indexing:concepts:retrieval|Next]]** |
| ====== Additional Resources ====== | ====== Additional Resources ====== | ||