Introduction: To avoid excessive use of MySQL automatic type conversion. MySQL performs an automatic type conversion, but if you can avoid this conversion, you get the performance the better. For example, if num_col is the integer data column, then following these queries will return the same result: ...
Lecture series of the MySQL Query Optimizer Query Optimizer
When you submit a query, when, MySQL will analyze it to see if we can do some optimization to deal with the query faster. This section describes how the query optimizer works. If you want to know MySQL optimization methods used, you can see MySQL reference manual.
Of course, MySQL query optimizer also uses the index, but it also uses other information. For example, if you submit a query as shown below, then no matter how much data tables, MySQL will implement its speed is very fast:
SELECT * FROM tbl_name WHERE 0;
In this case, MySQL View WHERE clause, recognizing that there is no rows meet the query, it simply does not consider the search data sheet. You can see that by providing an EXPLAIN statement, such a situation, this statement is to show their implementation of MySQL, but in fact no real implementation of the SELECT query some information. If you want to use EXPLAIN, only need to put the word EXPLAIN in front of 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
54com.Com
Under normal circumstances, EXPLAIN returns more information than the above information should be more, but also includes data tables for scanning the index, using the link type, each data table in the estimated number of rows of data need to examine other non-empty (NULL) Information .
How does it work optimizer
MySQL query optimizer has several goals, but the most important goal is to use the index as much as possible, and use the most stringent in the index to eliminate as many rows of data. Your ultimate goal is to submit SELECT statement to find rows of data, rather than to exclude rows of data. Optimizer attempts to exclude the data lines because it excluded the faster data row, then find the rows that match the criteria the sooner. If we can first conduct the most rigorous testing, the query can be performed more quickly. Suppose your query tested two data columns, each column has an index:
SELECT col3 FROM mytable
WHERE col1 = 'some value' AND col2 = 'some other value';
Suppose col1 test matches on th
