MySQL query performance optimization (7)-optimize specific types of queries

1. Optimize count() query

The count() aggregate function, and how to optimize the query that uses it, is probably one of the top 10 most misunderstood topics in mysql.

Count() is a special function that has two very different functions. It can count the number of values ​​in a certain column or the number of rows.
Statistical column value requires that the column value is non-empty. (Null is not counted, that is, the count of null value is 0)

Another use of count() is to count the number of rows in the result set. When mysql confirms that the expression value of the parentheses cannot be empty, it actually counts
the number of rows. The simplest is that when we use count(*), in this case, the wildcard character * will not expand to all
the columns as we guessed . In fact, it will ignore all the columns and directly count all of them. Rows.

If you count the number of different values ​​in the same column in the same query
select sum(if(color ='blue', 1, 0)) as BLUE, sum(if(color ='red', 1, 0)) as RED from items

select count(color ='blue' or null) blue, count(color ='red' or null) red, from items

Simple optimization

More complex optimization

Count() needs to scan a large number of rows (meaning access to a large amount of data) in order to obtain accurate results, so it is difficult to optimize. The only thing that can be done at the MySQL level is index coverage scan. If it is not enough, you should consider modifying the application Structure, consider adding a summary table

2. Optimize related queries

3. Optimize subqueries

4. Optimize GROUP BY / DISTINCT

5. Optimize GROUP BY WITH ROLLUP

6. Optimize limit paging

When paging operations in the system, usually use limit plus offset method to achieve, at the same time add the appropriate order by clause, if there is a corresponding index, usually the efficiency will be good; otherwise MySQL needs to do a lot of files Sort operation

7. Optimize SQL_CALC_FOUND_ROWS

8. Optimize union query