MySQL query optimizer in MySQL query optimization Series

This article mainly describes the MySQL query optimizer In the MySQL query optimization series. When you submit a query, the MySQL database will analyze it, it mainly depends on whether it can be used for optimization to make processing of the query faster.

This section describes how the query optimizer works. If you want to know the optimization methods used by MySQL (the best combination with PHP), you can refer to the Reference Manual for MySQL (the best combination with PHP.

Of course, the MySQL (best combination with PHP) query optimizer also uses indexes, but it also uses other information. For example, if you submit a query as shown in the following figure, MySQL (the best combination with PHP) can execute it very quickly regardless of the data table size:

 
 

  
  
SELECT * FROM tbl_name WHERE 0; 
 
 

In this example, MySQL (the best combination with PHP) views the WHERE clause and recognizes that there are no data rows that meet the query conditions. Therefore, data tables are not considered to be searched. You can see this situation by providing an EXPLAIN statement. This statement allows MySQL (the best combination with PHP) to display some information about the SELECT query that is executed but not actually executed. If you want to use EXPLAIN, you only need to put the EXPLAIN word before the SELECT statement:

 
 

  
  
MySQL (best combination with PHP)> explain select * FROM tbl_name WHERE 0 \ G
  
  
Id: 1
  
  
Select_type: SIMPLE
  
  
Table: NULL
  
  
Type: NULL
  
  
Possible_keys: NULL
  
  
Key: NULL
  
  
Key_len: NULL
  
  
Ref: NULL
  
  
Rows: NULL
  
  
Extra: Impossible WHERE
  
  
 
 
 

In general, EXPLAIN returns more information than the above information, it also includes non-NULL information such as the index used to scan the data table, the join type used, and the estimated number of data rows in each data table to be checked.

How the optimizer works

MySQL (the best combination with PHP) query optimizer has several goals, but the main goal is to use indexes as much as possible, and use the strictest index to eliminate as many data rows as possible. Your final goal is to submit a SELECT statement to find data rows, rather than exclude data rows.

The reason the optimizer tries to exclude data rows is that the faster it can exclude data rows, the faster it can find data rows that match conditions. If you can perform the strictest test first, the query can be executed faster. Assume that your query checks two data columns, each of which has an index:

 
 

  
  
SELECT col3 FROM mytable  
  
  
WHERE col1 = ’some value’ AND col2 = ’some other value’; 
 
 

Assume that the test on col1 matches 900 data rows, the test on col2 matches 300 data rows, and the test at the same time only has 30 data rows. First, test Col1 and there will be 900 data rows. Check them to find 30 matching records with the values in col2, and 870 of them will fail.

First, test col2 will have 300 data rows. Check them to find 30 records matching the values in col1. Only 270 of the records failed, therefore, less computing and disk I/O are required. The result is that the optimizer tests col2 first, because the overhead is smaller.

The above content is an introduction to the query optimizer In the MySQL query optimization series. I hope you will have some gains.