How MySQL query optimizer works

The MySQL query optimizer improves the query efficiency of our MySQL database. The following describes how the MySQL query optimizer works for your reference.

When you submit a query, MySQL will analyze it to see if some optimization can be done to make it faster to process the query. This section describes how the query optimizer works. If you want to know the optimization methods used by MySQL, you can refer to the MySQL reference manual.

Of course, the MySQL query optimizer also uses indexes, but it also uses other information. For example, if you submit a query as shown below, MySQL runs the query very quickly regardless of the data table size:

 
 

  
  
SELECT * FROM tbl_name WHERE 0;  
  
  
 
 
 

In this example, MySQL checks the WHERE clause and recognizes that there are no data rows that meet the query conditions. Therefore, it does not consider searching data tables. You can see this situation by providing an EXPLAIN statement, which allows MySQL to display some information about the SELECT query that has been executed but has not actually been executed. If you want to use EXPLAIN, you only need to put the EXPLAIN word before the SELECT statement:

 
 

  
  
mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G  
  
  
*************************** 1. row ***************************  
  
  
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 does the MySQL query optimizer work?

The MySQL query optimizer has several goals, but the primary goal is to use the index 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.

You can use the following guidance to help the optimizer make better use of indexes:

Try to compare columns with the same data type. When you use indexed data columns in a comparison operation, use columns of the same data type. The same data type has higher performance than different types. For example, INT and BIGINT are different. CHAR (10) is considered as CHAR (10) or VARCHAR (10), but is different from CHAR (12) or VARCHAR (12. If the data columns you compare have different types, you can use alter table to modify one of them to match their types.
 

Implementation of MySQL non-repeated Query

Special usage of the SELECT command in MySQL

Mysql command line parameters

Usage of MySQL conditional query statements

Use the Limit parameter to optimize MySQL queries