This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:strategies:home [2012/01/30 17:41] doc |
admin:indexing:strategies:home [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| Omnidex approaches indexing differently. Omnidex indexes all of the columns and then coordinates use of all of the indexes to fulfill the different aspects of a query. Some indexes will be used to satisfy table joins or criteria. Other indexes will be used to fulfill aggregations or ordering. Most database administrators are surprised to learn that complex SQL statements which join many tables and contain intricate criteria can often be fulfilled without ever accessing the underlying data. This approach allows Omnidex to process queries very quickly. It also reduces the load on the servers since accessing the data is a common cause of performance problems. | Omnidex approaches indexing differently. Omnidex indexes all of the columns and then coordinates use of all of the indexes to fulfill the different aspects of a query. Some indexes will be used to satisfy table joins or criteria. Other indexes will be used to fulfill aggregations or ordering. Most database administrators are surprised to learn that complex SQL statements which join many tables and contain intricate criteria can often be fulfilled without ever accessing the underlying data. This approach allows Omnidex to process queries very quickly. It also reduces the load on the servers since accessing the data is a common cause of performance problems. | ||
| - | Most indexing strategies are developed by analyzing the queries. Queries usually follow patterns and these patterns give clues to the best indexing approach. Analyze the queries to determine which columns are criteria in the WHERE clause, which tables are joined together in the FROM clause, which columns are aggregated or used in GROUP BY clauses, and which columns are used in the ORDER BY clause. Use the discussions in this section to create indexes for these columns and then run sample queries to assess their performance. [[admin:optimization:plans:home|Query plans]] will show how the indexes were used, and if the query is not fully optimized, revisit the indexing strategy as needed. You can also read about [[admin:indexing:advanced|Advanced Indexing Strategies]] for special optimization techniques. | + | Most indexing strategies are developed by analyzing the queries. Queries usually follow patterns and these patterns give clues to the best indexing approach. Analyze the queries to determine which columns are used in the WHERE clause, which tables are joined together in the FROM clause, which columns are aggregated or used in GROUP BY clauses, and which columns are used in the ORDER BY clause. Use the discussions in this section to create indexes for these columns and then run sample queries to assess their performance. [[admin:optimization:plans:home|Query plans]] will show how the indexes were used, and if the query is not fully optimized, revisit the indexing strategy as needed. |
| These basic indexing strategies will handle the majority of queries in most applications; however, queries can also be deceivingly unique. After using these strategies, it is recommended to review a wide range of queries to insure that they are properly optimized. | These basic indexing strategies will handle the majority of queries in most applications; however, queries can also be deceivingly unique. After using these strategies, it is recommended to review a wide range of queries to insure that they are properly optimized. | ||