This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:optimization:plans:configuration [2012/02/03 18:04] doc created |
admin:optimization:plans:configuration [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| Omnidex Query Plans have some configuration options that will increase the amount of information available. These are available using the SET EXPLAIN command in OdxSQL. The syntax of the SET EXPLAIN command is: | Omnidex Query Plans have some configuration options that will increase the amount of information available. These are available using the SET EXPLAIN command in OdxSQL. The syntax of the SET EXPLAIN command is: | ||
| - | SET EXPLAIN [COUNTS] [TEXT] [NODES] | + | SET EXPLAIN [COUNTS] [TEXT] |
| == COUNTS == | == COUNTS == | ||
| - | The COUNTS option will cause the explain to display row counts when known, and timings for certain steps. This option only effects query plans that are run after the query has completed. This is done by issuing the SELECT statement, letting it complete, and then typing the command "EXPLAIN" by itself. | + | The COUNTS option will display row counts and timings in the query plan after certain steps. This option only affects query plans that are run **after** the query has completed. First, issue the query in OdxSQL and let it run to completion, and then issue the "EXPLAIN" command by itself. |
| - | Counts and timings can be quite useful toward determine where query performance is most affected. The follow query plan shows the counts and timings for a well-optimized query: | + | Counts and timings can be quite useful in determining where query performance is most affected. The follow query plan shows the counts and timings for a well-optimized query: |
| <code> | <code> | ||
| Line 71: | Line 71: | ||
| == TEXT == | == TEXT == | ||
| - | The TEXT option displays more information about the expansion of the query due to the use of PowerSearch. [[admin:indexing:powersearch:home|PowerSearch]] allows a query to locate rows even if they do not exactly match the criteria using techniques like synonyms, misspellings, transpositions, word forms, etc. The TEXT option will show the exact search terms after the query was expanded. | + | The TEXT option displays more information about the expansion of the query due to the use of [[admin:indexing:powersearch:home|PowerSearch]]. PowerSearch allows a query to locate rows even if they do not exactly match the criteria using techniques like synonyms, misspellings, transpositions, word forms, etc. The TEXT option will show the exact search terms after the query was expanded. |
| - | The follow query plan shows the expanded search terms for a PowerSearch query. In this example, PowerSearch is being used to find all the synonyms and phonetic equivalents of William, and a wide misspelling search is being used for emails containing Vasquez. The query plan shows that several synonyms for William will be used, and two email addresses that are similar to Vasquez will be included in the search. | + | The follow query plan shows the expanded search terms for a PowerSearch query. In this example, PowerSearch is being used to find all the synonyms and phonetic equivalents of William, and the spelling variations of an email address containing Vasquez. The query plan shows several synonyms for William and two email addresses that are similar to Vasquez. |
| <code> | <code> | ||
| Line 106: | Line 106: | ||
| </code> | </code> | ||
| - | === NODES === | ||
| - | |||
| - | |||
| - | will provide a basic level of information, | ||
| - | 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 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. | ||
| - | |||
| - | 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. | ||
| - | |||
| - | ==== Obtaining a Query Plan ==== | ||
| - | |||
| - | Query plans are produced using the EXPLAIN command in OdxSQL. A plan can be obtain by preceding the SELECT statement with the word EXPLAIN, as shown in the example below: | ||
| - | |||
| - | <code> | ||
| - | > explain select name, phone, email from individuals; | ||
| - | ----------------------------------- SUMMARY ----------------------------------- | ||
| - | Select NAME, | ||
| - | PHONE, | ||
| - | |||
| - | from INDIVIDUALS; | ||
| - | |||
| - | Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) | ||
| - | Warnings: SEQUENTIAL_SCAN | ||
| - | ----------------------------------- DETAILS ----------------------------------- | ||
| - | Retrieve INDIVIDUALS sequentially; | ||
| - | Return INDIVIDUALS.NAME, INDIVIDUALS.PHONE, INDIVIDUALS.EMAIL; | ||
| - | ------------------------------------------------------------------------------- | ||
| - | </code> | ||
| - | |||
| - | A plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization settings or warnings that affect the query, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions. | ||
| - | |||
| - | 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. | ||
| - | |||
| ===== ===== | ===== ===== | ||
| ---- | ---- | ||
| \\ | \\ | ||
| - | **[[admin:optimization:reference|Next]]** | + | **[[admin:optimization:plans:optimization|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}} | ||