How to Tune SQL Statement with IN List Bind Variables for MySQL?

Discussion in 'MySQL' started by Richard To, Dec 10, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    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.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice