The following is an example shows a SQL statement with a variable on the IN List operator. The SQL retrieve records from EMPLOYEE table that (EMP_ID,@1) should match any value in a set of values on the right-hand side. select * from employee where (emp_id,@1) in ((1000000,'a'),(2000000,'b'),(3000000,'c')) Here the following are the query plans in Tosska proprietary tree format, it takes 19 seconds to finish. The query plan shows a full table scan of EMPLOYEE, it means MySQL cannot decompose the IN list syntax into a better syntax for cost evaluation and no index scan is used. Let me rewrite the IN list into multiple OR conditions in the following: select * from employee where ( ( emp_id = 1000000 and @1 = 'a' ) or ( emp_id = 2000000 and @1 = 'b' ) or ( emp_id = 3000000 and @1 = 'c' ) ) Now, MySQL can utilize Single Row (constant) index search. The speed now is 0.0059 second and is much faster than original SQL. This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3200 times faster than the original SQL. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
