There may be some business requirements that need to compare certain part of a column as a data retrieval criteria. Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern “Acco” start from 5 character of the emp_name.
select *
from employee
where substr(emp_name,5,4)=‘Acco’
Here the following are the query plans of this SQL, it takes 17 seconds to finish. The query shows a “Full Table Scan Employee”
You can see that this SQL cannot utilize index scan even the emp_name is indexed field. Let me add a “Force Index(emp_name_inx)“ hints to the SQL and hope it can help MySQL SQL optimizer to use index scan, but it fails to enable the index scan anyway, so I add one more dummy condition emp_name >= ‘ ‘ , it is an always true condition that emp_name should be greater or equal to a smallest empty character.
select *
from employee force index(emp_name_inx)
where substr(emp_name,5,4) = ‘Acco’
and emp_name >= ‘ ‘
Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows that an Index Range Scan is used now.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is almost 6 times faster than the original SQL.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/