This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:optimization:plans:home [2012/02/02 16:19] doc |
admin:optimization:plans:home [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | ====== Administration ====== | + | ====== Administration: Optimizing Queries ====== |
| - | ===== Optimizing Queries ===== | + | ===== Query Plans ===== |
| Line 15: | Line 15: | ||
| ---- | ---- | ||
| - | \\ | + | |
| ==== Overview ==== | ==== Overview ==== | ||
| - | Omnidex always optimizes a query as well as it can using the Omnidex indexes; however, if the indexes are not enough, Omnidex will complete the query without indexes, insuring the correct result. In fact, Omnidex can process queries even when no Omnidex indexes are available at all. In this way, Omnidex is first and foremost a SQL Engine for both relational and non-relational, or NoSQL, databases. | + | Omnidex always optimizes a query as well as it can using the Omnidex indexes; however, if the indexes are not enough, Omnidex will complete the query without indexes, insuring the correct result. In fact, Omnidex can process queries even when no Omnidex indexes are available. In this way, Omnidex is first and foremost a SQL Engine for both relational and non-relational, or NoSQL, databases. |
| - | Omnidex will evaluate the query and identify where indexes can be used. Omnidex evaluates the tables and their join relationships. Omnidex evaluates criteria, including nested queries, SQL functions and complex Boolean operations. Omnidex evaluates group by and order by, both to perform aggregations and to avoid unnecessary sorting of data. Omnidex even considers whether indexes can be used to return columns in the result set, avoiding accessing the data whenever possible. If there are not indexes to satisfy any of these steps, it will process then without the aid of indexing. | + | Omnidex will evaluate the query and identify where indexes can be used. Omnidex evaluates the tables and their join relationships. Omnidex evaluates criteria, including nested queries, SQL functions and complex Boolean operations. Omnidex evaluates GROUP BY and ORDER BY clauses, both to perform aggregations and to avoid unnecessary sorting of data. Omnidex even considers whether indexes can be used to return columns in the result set, avoiding access to the database whenever possible. In any of these situations, if there are not indexes to satisfy a step, it will process that part of the query without the aid of indexing. |
| The optimization plan for a query shows a sequence of steps, including table joins, processing criteria, aggregating data, and retrieving from the database. The ideal with Omnidex optimization is to avoid retrieving from the database if possible, and to fully optimize the query solely through the Omnidex indexes. If non-indexed steps are required, optimization tries to minimize these steps as much as possible. | The optimization plan for a query shows a sequence of steps, including table joins, processing criteria, aggregating data, and retrieving from the database. The ideal with Omnidex optimization is to avoid retrieving from the database if possible, and to fully optimize the query solely through the Omnidex indexes. If non-indexed steps are required, optimization tries to minimize these steps as much as possible. | ||
| Line 45: | Line 45: | ||
| </code> | </code> | ||
| - | A plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, and any optimization settings or warnings that affect the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and steps that are repeated or looped are indented. | + | The EXPLAIN command can also follow a query in OdxSQL. It will provide a query plan for the query that was just executed. This approach has the advantage of having more statistics about the query; however, generating the query plan before the query is run has the advantage of checking a query before it consumes too many resources. |
| + | |||
| + | A plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, and any optimization warnings or suggestions about the query. The Details section shows the steps that will be executed to satisfy the query. | ||
| In the simple example above, three columns are retrieved from the INDIVIDUALS table. Since no criteria was provided in a WHERE clause, the details of the plan show that the table is retrieved sequentially and the columns are returned. A simple warning indicates that a sequential scan is taking place, which in this case cannot be avoided. | In the simple example above, three columns are retrieved from the INDIVIDUALS table. Since no criteria was provided in a WHERE clause, the details of the plan show that the table is retrieved sequentially and the columns are returned. A simple warning indicates that a sequential scan is taking place, which in this case cannot be avoided. | ||
| Line 53: | Line 55: | ||
| ---- | ---- | ||
| \\ | \\ | ||
| - | **[[admin:optimization:reference|Next]]** | + | **[[admin:optimization:plans:reference|Next]]** |
| ====== Additional Resources ====== | ====== Additional Resources ====== | ||
| See also: | See also: | ||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | + | {{page>:admin:optimization:see_also&nofooter&noeditbtn}} |
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||