Is better use this SQL code suppose the right index in apply on the column!!
Suppose constant is a input from a textfield!!
where lower(column) like 'Constant%' or lower(column) like '%Constant%'
where lower(column) like '%Constant%'
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
2) run another query with
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.