The WHERE clause of the SELECT statement determines how the database server accesses tables. The conditional expression in the WHERE clause is commonly called a filter.
Use the following guidelines to evaluate filter clauses in query and transaction statements:
The MATCHES and LIKE keywords support wildcard matches, which are technically known as regular expressions. Some regular expressions are more difficult than others for the database server to process. A wildcard in the initial position, as in the following example (find customers whose first names do not end in y), forces the database server to examine every value in the column:
SELECT * FROM customer WHERE fname NOT LIKE '%y'
Because you cannot use an index with such a filter, the database server must access the table in this example sequentially.
If a difficult test for a regular expression is essential, avoid combining it with a join. If necessary, process the single table, applying the test for a regular expression to select the required rows. Save the result in a temporary table, and join that table to the others.
Regular-expression tests with wildcards in the middle or at the end of the operand do not prevent the use of an index when one exists.
A filter based on a noninitial substring of a column requires each value in the column to be tested, as the following example shows:
SELECT * FROM accts WHERE code[4,5] > '50'
A standard index cannot be used to evaluate such a filter.
If queries must often search standard columns for noninitial substrings, the table definition might be at fault. For example, an accounting system might use a long string of numbers as hierarchical account codes. Each level of the hierarchy should be a separate column.
You can improve performance for filters that contain aggregate statements if you construct appropriate GK-indexes for the queries.
A filter-aggregate query has these requirements: