How to tune SQL with Like '%ABC%'

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

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    87
    Likes Received:
    36
    Trophy Points:
    18
    Gender:
    Male
    It is common we come across SQL with Like '%Alex%' in MySQL that no index will be used.
    Here is one of the rewrite solution that may help in your environment.

    Example
    select *
    from employee
    where emp_name like '%Alex%';

    If emp_name is indexed, we can rewrite the SQL into the following syntax.

    Rewritten SQL
    select *
    from employee force index (emp_name_inx)
    where emp_name like '%Alex%'
    and emp_name>'';
     

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