Some business requirements may need to compare the lower case of an indexed column to a given string as a data retrieval criterion. Here is an example SQL that retrieves records from the EMPLOYEE table employee if the lower case of the name is equal to the string ‘richard’. select * from employee where LCASE(emp_name)='richard' 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 if the emp_name is an indexed field. Let me add a 'Force Index(emp_name_inx)' hint 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, it is used to increase the cost of not using emp_name_inx index. There is another condition added “emp_name is null” to correct this condition if emp_name is a null value. select * from employee force index(EMPS_NAME_INX) where LCASE(emp_name) = 'richard' and ( emp_name >= '' or emp_name is null ) Here is the query plan of the rewritten SQL and it is running much faster. The new query plan shows that an Index Scan is used now and takes 2.79 seconds only. This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
