We used to use FORCE INDEX hints to enable an index search for a SQL statement if a specific index is not used. It is due to the database SQL optimizer thinking that not using the specific index will perform better. But enabling an index is not as simple as just adding an index search in the query plan, it may entirely change the structure of the query plan, which means that forecasting the performance of the new Force Index hints is not easy. Here is an example to show you how to use FORCE INDEX optimization hints to tune a SQL statement. A simple example SQL that updates EMP_SUBSIDIARY if the emp_id is found in EMPLOYEE with certain criteria. update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)') where emp_id in (SELECT emp_id FROM EMPLOYEE WHERE emp_salary <1000000 and emp_grade<1150) Here the following is the query plan of this SQL, it takes 18.38 seconds. The query shows a Full Table Scan of EMPLOYEE and then Nested Loop to EMP_SUBSIDIARY with a Unique Key Lookup of Emp_sub_PK index. We can see that the filter condition “emp_salary <1000000 and emp_grade<1150” is used for the full table scan of EMPLOYEE. The estimated “filtered (ratio of rows produced per rows examined): 3.79%”, it seems the MySQL SQL optimizer is failed to use an index to scan the EMPLOYEE table. We should consider forcing MySQL to use either one of emp_salary or emp_grade index. Unless you fully understand the data distribution and do a very precise calculation, otherwise you are not able to tell which index is the best? Let’s try to force the index of emp_salary first. update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)') where emp_id in (select emp_id from EMPLOYEE FORCE INDEX(`emps_salary_inx`) where emp_salary < 1000000 and emp_grade < 1150) This SQL takes 8.92 seconds and is 2 times better than the original query plan without force index hints. Let’s try to force the index of emp_grade again. update EMP_SUBSIDIARY set emp_name=concat(emp_name,'(Headquarter)') where emp_id in (select emp_id from EMPLOYEE FORCE INDEX(`emps_grade_inx`) where emp_salary < 1000000 and emp_grade < 1150) Here is the result query plan of the Hints FORCE INDEX(`emps_grade_inx`) injected SQL and the execution time is reduced to 3.95 seconds. The new query plan shows an Index Range Scan of EMPLOYEE by EMP_GRADE index, the result is fed to a subquery2(temp table) and Nested Loop to EMP_SUBSIDIARY for the update. This query plan’s estimated cost is lower and performs better than the original SQL. It is due to the limited plan space in the real-time SQL optimization process, so this query plan cannot be generated for the original SQL text, so manual hints injection is necessary for this SQL statement to help MySQL database SQL optimizer to find a better query plan. This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the Hints injected SQL is more than 4.6 times faster than the original SQL. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
