The LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching. Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern like “Mary%”. If the emp_name is indexed, the following SQL will utilize Index Range Scan of the emp_name and the speed of the SQL will be fine. select * from employee where emp_name like 'Mary%'; If user is looking for emp_name with pattern like '%Mary%', MySQL SQL Optimizer cannot use emp_name index to speed up the process, full table scan is normally be used and the performance will be bad too. select * from employee where emp_name like '%Mary%'; Here the following are the query plan of this SQL, it takes 18.8 seconds to finish. The query shows a “Full Table Scan” of employee table. You can see that this SQL cannot utilize index scan even the emp_name is indexed. 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 always greater or equal to a smallest empty character. select * from employee force index(emp_name_inx) where emp_name like '%Mary%' 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 rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is almost 5 times faster than the original SQL. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
