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 COUNT(*) statements ?

Discussion in 'MySQL' started by Richard To, Sep 7, 2020.

  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 we used to count the number of records in a table. You may encounter unexpected performance degradation in certain situations.

    Here is an example SQL that count number of records from EMPLOYEE table. There are number of indexes are built such as emp_id, emp_dept, emp_grade, emp_hire_date and etc....

    SELECT COUNT(*)
    FROM EMPLOYEE;
    [​IMG]
    You can see MySQL SQL Optimizer use a Full Index Scan of EMP_HIRE_DATE index, the performance is bad since unnecessary random reads is needed and it takes 3 minutes and 6 seconds to count a 3 million records in my computer. I want to make use of Index Range Scan for specific index, let me rewrite the above SQL into the following syntax. If you know EMP_GRADE is indexed and it is not a nullable column, you can add a dummy condition EMP_GRADE>=’’. It fools MySQL SQL optimizer to consider using EMP_GRADE range index to retrieve the records and it is successfully generate a new plan in the following:

    select COUNT(*)
    from EMPLOYEE
    where EMP_GRADE >= ''

    Here the following is the query plan of this SQL, it takes 2.6 seconds to finish. The query shows an “Index Range Scan” of employee table.
    [​IMG]
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is almost 71 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