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

How to Tune SQL with OR statements?

Discussion in 'MySQL' started by Richard To, Sep 14, 2020 at 5:15 AM.

  1. Richard To

    Richard To New Member

    Joined:
    Sep 18, 2017
    Messages:
    25
    Likes Received:
    9
    Trophy Points:
    3
    Gender:
    Male
    It is common that the performance is not good if a SQL statement with OR conditions. Let’s have an example show you how to tune those SQL statements in certain situations.
    Here is an example SQL that extract records from EMPLOYEE table if (emp_grade < 1050 or emp_id<730000). Emp_grade and emp_id are indexed and they are not null field.

    select * from employee
    where emp_grade < 1050 or emp_id<730000
    [​IMG]
    You can see MySQL SQL Optimizer use an Index Merge of emps_grade_inx and employee_pk to process the SQL, the performance is not good as expected since the result set is quite big for sort_union operation. It takes more than 40 seconds to finish the data retrieval. Let me rewrite the OR condition into the following UNION ALL statement, please make sure the emp_grade and emp_id are not null column, otherwise it may generate error result. The rewrite is simple that the first part extract data with emp_grade<1050, the second part of the UNION ALL retrieve records that satisfied with emp_id<730000, but it is not retrieved in the first part of the UNION ALL.

    select *
    from employee
    where emp_grade < 1050
    union all
    select *
    from employee
    where not ( emp_grade < 1050 )
    and emp_id < 730000

    Here the following is the query plan of this SQL, it takes 12.46 seconds to finish. The query shows two “Index Range Scan” of EMPLOYEE_PK and EMPS_GRADE_INX to the employee table.
    [​IMG]
    This kind of rewrites 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. There are some other rewrites shown in this screen with comparable results too.

    https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2

    [​IMG]
     
    shabbir likes this.

Share This Page