How to tune Substr(emp_name,x,y) SQL statement?

Discussion in 'MySQL' started by Richard To, Aug 24, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    There may be some business requirements that need to compare certain part of a column as a data retrieval criteria.

    Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern “Acco” start from the fifth character of the emp_name string

    select *
    from employee
    where substr(emp_name,5,4)='Acco'

    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 the emp_name is indexed field. Let me add a “Force Index(emp_name_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_name >= '' , it is an always true condition that emp_name should be greater or equal to a smallest empty string.

    select *
    from employee force index(emp_name_inx)
    where substr(emp_name,5,4) = 'Acco'
    and emp_name >= ''

    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 now.
    This kind of rewrite can be archived by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is almost 6 times faster than the original SQL.
    shabbir likes this.

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