Avoid difficult regular expressions
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'You cannot use an index with such a filter, so the table in this example must be accessed sequentially.
If a difficult test for a regular expression is essential, avoid combining it with a join. If necessary, process the single table and apply the test for a regular expression to select the desired 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.