How to Tune SQL Statements with CONCAT Operator for MySQL?

Discussion in 'MySQL' started by Richard To, Feb 19, 2021.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    There may be some business requirements that need to compare concatenate strings and column with a given unknown length of the bind variable.

    Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT tables where employee’s department ID must concatenate two strings before it is compared to an unknown length of variable @dpt_var

    select * from employee,department
    where concat(concat('A',emp_dept),'B') = @dpt_var
    and emp_dept= dpt_id​

    Here the following are the query plans of this SQL, it takes 23.8 seconds to finish. The query shows a “Full Table Scan Employee” to nested loop Department table.
    You can see that this SQL cannot utilize index scan even the emp_dept is an indexed field. Let me add a “force index(EMPS_DPT_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_dept >= '' , it is an always true condition that emp_dept should be greater or equal to a smallest empty character. It is to fool MySQL SQL optimizer that emp_dept’s index is a reasonable step.

    select *
    from employee force index(EMPS_DPT_INX),
    where concat(concat('A',emp_dept),'B') = @dpt_var
    and emp_dept >= ''
    and emp_dept = dpt_id​

    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 for Employee table first and then nested-loop Department table.
    This kind of rewrite can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 3 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