This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:expressioncols:optimization [2012/02/05 15:39] doc |
admin:features:expressioncols:optimization [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 26: | Line 26: | ||
| === Indexing Expressions === | === Indexing Expressions === | ||
| + | Standard Omnidex indexes are appropriate for optimizing criteria where a column is being compared to one or more values. For example, standard Omnidex indexes are excellent for optimizing the criteria, "STATE = 'MA' and CITY = 'Boston'". By default, Omnidex indexes cannot be used to optimize criteria where an expression is being compared to one or more values. For example, standard Omnidex indexes cannot be used to optimize the criteria, "((QUANTITY * UNIT_COST) * TAX_RATE) + COMMISSION) > 100.00". | ||
| + | In these situations, Omnidex will look for an expression-based column that matches the expression in the SQL statement. If one is found, Omnidex will look to see if that expression-based column has been indexed. If an index is found, Omnidex will optimize the query by using that index. This has two advantages. First, it gains the speed of an indexed search, and it prevents the expression from be recalculated for each row as it is retrieved. | ||
| + | This same technique is appropriate for many types of expressions. For example, a search for area code using criteria of "SUBSTRING(PHONE from 2 for 3) = '303'" can be optimized using a similarly constructed expression-based column. A search for age using criteria of "$COMPARE_DATES(BIRTHDATE, CURRENT_DATE, 'YY') >= 24" can be similarly optimized. This technique creates a wide variety of new oppportunities for optimizing queries using Omnidex indexes. | ||
| ==== ==== | ==== ==== | ||