This section is mainly for some specific types of optimization queries:
(1) count query optimization;
(2) Related query
(3) Subquery
(4) GROUP BY and DISTINCT optimization
(5) LIMIT paging optimization
The role of the COUNT() aggregate function:
(1) Count the number of values in a certain column, and you can also count rows number. It should be noted that when counting column values, the column value must be non-null (not counting NULL)
(2) Count the number of rows in the result set. When the column value cannot be empty, the number of rows in the table is counted. But to ensure that you must use COUNT() to get the number of rows in the result set. Wildcard will directly ignore all column values and directly calculate the number of rows for optimization.
For the MyISAM storage engine, COUNT(*) is very fast when the where query conditions are not limited in a single table, because MyISAM itself has already stored the total number of rows. When there is a where qualification, query statistics are also required.
The following is a simple optimization usage example:
(1) Optimization 1:
) feature, we can use count() - (id
(2) Optimization 2:In addition, there is another optimization method which is to use covering index.
(2) Ensure that
expressions in any Group by and order by operations only involve columns in one table. In this wayMySQLit is possible to use index optimization
select product, count(*) from orders group by product;
The above is the detailed content of High-performance MySQL-detailed optimization of specific types of queries. For more information, please follow other related articles on the PHP Chinese website!