1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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 New Member

    Joined:
    Sep 18, 2017
    Messages:
    25
    Likes Received:
    9
    Trophy Points:
    3
    Gender:
    Male
    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”

    [​IMG]
    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.
    [​IMG]
    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.

    https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
    [​IMG]
     
    shabbir likes this.

Share This Page