MySQL Performance optimization learning note (3) Common SQL statement optimizations

One, max () optimization
Mysql> explain select Max (payment_date) from payment;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| 1 | Simple | Payment | All | NULL | NULL | NULL | NULL |       14394 | |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
1 row in Set (0.00 sec)

Mysql> CREATE index Idx_paymentdate on payment (payment_date);
Query OK, 0 rows affected (0.09 sec)
records:0 duplicates:0 warnings:0

Mysql> explain select Max (payment_date) from payment;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select Tables Optimized Away |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
1 row in Set (0.00 sec)

The efficiency is higher in terms of the number of rows returned.

Ii. optimization of Count ()

2005-year and 2006 film production respectively

Mysql> Select COUNT (release_year=2006 or null) as ' film2006 ', count (release_year=2005 or null) as ' film2005 ' from film;
+----------+----------+
| film2006 | film2005 |
+----------+----------+
|        1000 | 0 |
+----------+----------+
1 row in Set (0.00 sec)


Third, sub-query method
Usually, you need to optimize the query to join query, but in the optimization need to pay attention to whether the correlation key exists a one-to-many relationship, pay attention to duplicate data (distinct)

Iv. GROUP By Query method
The group by may have temporary tables (using temporary), file ordering (using Filesort), etc., affecting efficiency.
You can save IO by correlating subqueries to avoid temporary table and file sorting
Before rewriting
Select Actor.first_name,actor.last_name,count (*)
From Sakila.film_actor
INNER JOIN Sakila.actor using (actor_id)
Group BY FILM_ACTOR.ACTOR_ID;
After rewriting
Select actor.first_name,actor.last_name,c.cnt
From Sakila.actor INNER JOIN (
Select Actor_id,count (*) as CNT from Sakila.film_actor GROUP by
actor_id
) as C using (actor_id);

Five, limit query
Limit is often used for paging, often accompanied by an ORDER BY clause, so most of the time using filesorts will cause a lot of IO problems
1. Use an indexed column or primary key for an order by operation
2. Record the last returned primary key and use primary key filtering at the next query
There is a restriction that the primary key must be sequential and sequential, and if the primary key appears to be empty, it may result in less than 5 listings on the final page, and the workaround is to append a column to ensure that the column is self-incremented and the index is increased.

MySQL Performance optimization learning note (3) Common SQL statement optimizations