MySQL Condition Optimization example

MySQL Condition Optimization example

Some friends tried the following and asked questions:

Merge having conditions into where conditions
Advantages:
It facilitates unified and centralized resolution of condition clauses, saving time for multiple resolutions.
Note:
Not all HAVING conditions can be incorporated into the WHERE condition. Only when the GROUPBY condition or clustering function does not exist in the SQL statement can the HAVING condition and WHERE condition be merged.

Mysql> explain extended select id, genre from movies where id> 10 having genre> 1000;
+ ---- + ------------- + -------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + -------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
| 1 | SIMPLE | movies | ALL | PRIMARY | NULL | 107230 | 100.00 | Using where |
+ ---- + ------------- + -------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
1 row in set, 1 warning (0.00 sec)
Mysql> show warnings \ G;
* *************************** 1. row ***************************
Level: Note
Code: 1003
Message:/* select #1 */select 'portal _ 19 '. 'movies '. 'id' AS 'id', 'portal _ 19 '. 'movies '. 'genre' AS 'genre' from 'portal _ 19 '. 'movies' where ('portal _ 19 '. 'movies '. 'id'> 10) having ('portal _ 19 '. 'movies '. 'genre'> 1000)
1 row in set (0.00 sec)
Mysql> explain extended select id, genre from movies where id> 10 and genre> 1000;
+ ---- + ------------- + -------- + ------ + --------------------- + ------ + --------- + ------ + -------- + ---------- + --------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + -------- + ------ + --------------------- + ------ + --------- + ------ + -------- + ---------- + --------------- +
| 1 | SIMPLE | movies | ALL | PRIMARY, genre_index | NULL | 107230 | 67.72 | Using where |
+ ---- + ------------- + -------- + ------ + --------------------- + ------ + --------- + ------ + -------- + ---------- + --------------- +
1 row in set, 1 warning (0.00 sec)
Mysql> show warnings \ G;
* *************************** 1. row ***************************
Level: Note
Code: 1003
Message:/* select #1 */select 'portal _ 19 '. 'movies '. 'id' AS 'id', 'portal _ 19 '. 'movies '. 'genre' AS 'genre' from 'portal _ 19 '. 'movies' where ('portal _ 19 '. 'movies '. 'id'> 10) and ('portal _ 19 '. 'movies '. 'genre'> 1000 ))
1 row in set (0.00 sec)
The execution sequence is the same, but the optimized filtered value and is smaller than having.
Mysql> select id, genre from movies where id> 10 having genre> 1000;
......
72187 rows in set (0.36 sec)
Mysql> select id, genre from movies where id> 10 and genre> 1000;
......
72187 rows in set (0.37 sec)
The execution time after optimization is 0.01 s longer than that after optimization. Why? (Mysql does not support incorporating having conditions into where conditions)
---
--- Answer:
--- 1 this time may not necessarily serve as the basis for "precise comparison"
--- 2 The "precise comparison" method is at least to calculate the mean multiple times.
--- 3 Method 1: Compare the mean value of the whole process from opening the table to getting data: loop, execute "flush table movies" each time, accumulate the time of each query, and then calculate the mean value.
--- 4 Method 2: remove the process such as opening the table, and only calculate the average value of the process for obtaining data: the query is executed once first, and the time is not counted. Then accumulate the time of each query cyclically, and then calculate the mean value.
--- 5 method 3: Methods 1 and 2 can be merged. Calculate "time for opening a table at a time + repeating multiple query statements = total time", and then calculate the mean value.
--- 6 in essence, before and after optimization, all tables are scanned, so there will be no difference. But the filtered value is different, which should be a bug.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: