Example Analysis of SQL sentence Optimization in MySQL
This article mainly introduces the example analysis of SQL sentence optimization in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!
The sql is as follows: sql enforces the into_time index
# Time: 2017-02-14T11:35:01.594499+08:00
# User@Host: oms_ readonly[ oms _ readonly] @ [10.44.xxx.xxx] Id: 41636892
# Query_time: 9.299612 Lock_time: 0.000124 Rows_sent: 20 Rows_examined: 2439330
SET timestamp=1487043301
Select * from customers force index (`into_ time`) where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_ time` show table status like 'customers'\ G
* * 1. Row *
Name: customers
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2504609
Avg_row_length: 710
Data_length: 1780383744
Max_data_length: 0
Index_length: 1253048320
Data_free: 6291456
Auto_increment: 2546101
Create_time: 2017-01-07 01:59:34
Update_time: 2017-02-14 13:58:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
There are about 2.5 million rows in the table. Check that into_time select count (*) from customers where `into_ time` explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_ time`
