mysql performance optimization max, count optimization

Note: Add explain before executing the SQL statement to view the execution plan of MySQL
Database: the sakila database officially provided by MySQL

Max optimization:

For example: Query the last payment time

explain select max(payment_date) from payment \G;

The query type is simple, no index is used, the number of scanned rows is more than 10,000, and it takes 0.02sec

Optimization method:

Create an index on the payment_date column

create index idx_paydate on payment(payment_date);

Then execute this sql statement and find:

The reason for this execution result is: because the index is arranged sequentially, through the index, you can immediately know what the last one is

Count optimization

For example: find out simultaneously in a SQL statement What are the number of movies in 2006 and 2007?
Wrong way:

select count(release_year = '2006' OR release_year = '2007') from film;

The number of movies in 2006 and 2007 cannot be calculated separately

select count(*) from film where release_year = '2006' and release_year = '2007'

release_year cannot be 2006 and 2007 at the same time, Therefore, it is logically wrong.

Query optimization is as follows:

select count(release_year='2006' or null) as '2006年的电影数量',count(release_year='2007' or null) as '2007年的电影数量' from film;

Description, in sql,count(*)andcount(a certain column), the execution results are sometimes different, becausecount(*)contains null, and if the other one is null, it will not be counted.
Using this feature, the year other than 2006 will be recorded as null. The execution result is as shown in the figure below

Note: You can view the execution of MySQL by adding explain before executing the SQL statement. Plan
Database: MySQL officially provided sakila database

Max optimization:

For example: Query the last payment time

explain select max(payment_date) from payment \G;

Query The type is simple, no index is used, the number of scanned rows is more than 10,000, and it takes 0.02sec

Optimization method:

Create an index on the payment_date column

create index idx_paydate on payment(payment_date);

Then after executing this sql statement, I found:

The reason for this execution result is: because the index is arranged in order, through the index, you can immediately know what the last one is

Count Optimization

For example: Find out the number of movies in 2006 and 2007 at the same time in one SQL statement
Error method:

select count(release_year = '2006' OR release_year = '2007') from film;

Cannot be calculated separately The number of movies in 2006 and 2007

select count(*) from film where release_year = '2006' and release_year = '2007'

release_year cannot be 2006 and 2007 at the same time, so the logic is wrong

The query is optimized as follows:

select count(release_year='2006' or null) as '2006年的电影数量',count(release_year='2007' or null) as '2007年的电影数量' from film;

Explanation, In sql, the execution results ofcount(*)andcount(a certain column)are sometimes different, becausecount(*)contains If it is null, if the other one is null, it will not be counted.
Using this feature, the year that is not 2006 will be recorded as null, and the execution result is as shown in the figure below

The above is the content of max and count optimization of mysql performance optimization, and more For related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!