1025WHERE execution sequence and MySQL query optimizer

Transfer from http://blog.csdn.net/zhanyan_x/article/details/25294539

--Where execution order
--filter more than put in front, and then easier to match, from left to right to execute; The optimizer is optimized intelligently, without user processing
--How to view the optimized statements
EXPLAIN EXTENDED
SELECT Sql_no_cache * from db.table
WHERE Is_day=1 and DATE (ex_date) = ' 2015-07-01 ';

SHOW WARNINGS;

--The first one is actually slower than the second.
EXPLAIN EXTENDED
SELECT Sql_no_cache * from TABLE
WHERE Is_day=1 and run_date= ' 2015-07-01 '

EXPLAIN EXTENDED
SELECT Sql_no_cache * from TABLE
WHERE run_date= ' 2015-07-01 ' and Is_day=1

This article mainly describes the MySQL query optimizer series of MySQL query optimizer, when you commit to a query, the MySQL database will analyze it, mainly to see whether it can be used to do some optimizations to make the query faster processing.

This section describes how the query optimizer works. If you want to know the optimization method MySQL uses, you can view the MySQL reference manual.

Of course, the MySQL query optimizer also uses the index, but it also uses some other information. For example, if you submit a query like the one shown below, MySQL executes it very quickly regardless of the size of the data table:

SELECT * from Tbl_name WHERE 0;

In this example, MySQL looks at the WHERE clause and realizes that there are no rows of data that meet the query criteria, so the search data table is not considered at all. You can see this by providing a explain statement that allows MySQL to show some information about the select query that it executes but actually does not really execute. If you want to use explain, just put the explain word in front of the SELECT statement:

Mysql> EXPLAIN SELECT * from Tbl_name WHERE 0\g id:1 select_type:simple table:null type:null p Ossible_keys:null key:null key_len:null ref:null rows:null extra:impossible WHERE

Typically, explain returns more information than the information above, including the index used to scan the data table, the type of junction used, and non-null (NULL) information, such as the number of data rows estimated to be checked in each data table.

How the optimizer works

The MySQL query optimizer has several goals, but the primary goal is to use the index as much as possible, and use the most restrictive indexes to eliminate as many rows of data as possible. Your ultimate goal is to submit a SELECT statement to find rows of data instead of excluding rows of data.

The reason the optimizer tries to exclude rows of data is that the faster it excludes rows of data, the faster it is to find rows that match the criteria. If you are able to perform the most rigorous tests first, queries can be executed faster. Suppose your query examines two columns of data, each with an index on it:

SELECT col3 from mytable WHERE col1 = ' Some value ' and col2 = ' some other value ';

Assuming that the test on col1 matches 900 rows of data, the test on col2 matches 300 rows of data, while the simultaneous test results in only 30 rows of data. Test Col1 will have 900 rows of data, you need to check that they find 30 of the values in the Col2 match records, 870 times it is a failure.

Testing col2 will have 300 rows of data, and you need to check that they find 30 of the records that match the values in col1, and only 270 are unsuccessful, so you need less compute and disk I/O. As a result, the optimizer tests the col2 first because it costs less.

1025WHERE execution sequence and MySQL query optimizer