chiperortiz chiperortiz - 29 days ago 13
MySQL Question

MySQL ilike performance on OR using index is better than %%?

Is better use this SQL code suppose the right index in apply on the column!!

Suppose constant is a input from a textfield!!

select ...
from .....
where lower(column) like 'Constant%' or lower(column) like '%Constant%'


Is better than?

select ...
from .....
where lower(column) like '%Constant%'


In the first code i try to match a "constant" using like but using a index trying being lucky to find a match and later i try to do a full match!!

All i want is my performance is not decreased! I mean if both queries runs in the same time or if the query can sometimes get a performance upgrade is OK with me

Answer

No, this would not improve the query performance significantly. MySQL will match the WHERE clause "per row" and therefore inspect ALL of the conditions before proceeding to the next row. Hitting the index first may slightly increase the performance if there is a match, but this gain will most likely be overtaken by the double evaluation in case the first condition does not match.

What could have helped is :

1) run the query with like 'Constant%'

2) run another query with like '%Constant%'

in which case, the first one may be accelerated if there is a match. However, you will most likely suffer from the overhead and perform worse in 2 queries than in one.

Moreover, the LIKE operator is case insensitive. Therefore, the lower(column) is unnecessary.

Meanwhile, if you expect your data to match principally on the first condition, and rarely on the second, then YES, this would lead to an increase as the second condition is not evaluated.