Mysql SELECT query speed Optimization

In general, to make a slow select... where faster, you should first check whether an index can be added. References between different tables are usually completed through indexes. You can use the explain statement to determine which indexes the select statement uses. See section 7.4.5, "How to Use indexes in mysql tutorials" and section 7.2.1, "explain syntax (get information about select )".

Below are some general suggestions for accelerating queries to the myisam table:

· To help mysql optimize queries, run analyze table or myisamchk -- analyze on a table loaded with data. In this way, the average number of rows with the same value is indicated for each Index Update (of course, if there is only one index, this is always 1 .) Mysql uses this method to determine which index to select when you join two tables based on a constant expression. You can use show index from tbl_name and check the cardinality value to check the analysis results. Myisamchk -- description -- verbose can display index distribution information.

· To sort an index and data according to an index, use myisamchk -- sort-index -- sort-records = 1 (if you want to sort the data on index 1 ). If there is only one index and you want to read all records in the order of the index, this is a good way to make the query faster. However, it takes a long time to sort a large table by this method for the first time!

7.2.4. How does mysql optimize the where clause?
This section discusses the optimization for processing the where clause. The select statement is used in this example, but the same optimization applies to the where clause in the delete and update statements.

Note that the mysql optimizer is constantly working, so this section is not complete. Mysql implements a lot of optimizations, which are not detailed in this article.

Some optimizations executed by mysql are listed below:

· Remove unnecessary parentheses:

· (A and B) and c or (a and B) and (c and d) ·-> (a and B and c) or (a and B and c and d) · constants overlap:

· (A <B and B = c) and a = 5 ·-> B> 5 and B = c and a = 5 · remove constant conditions (due to constant overlap ):

· (B> = 5 and B = 5) or (B = 6 and 5 = 5) or (B = 7 and 5 = 6) ·-> B = 5 or B = 6 · the constant expression used by the index is calculated only once.

For myisam and heap tables, no where count (*) in a single table is directly retrieved from the table. When only one table is used, the not null expression is also used.
Early detection of invalid constant expressions. Mysql quickly detects that some select statements are impossible and no rows are returned.
If you do not use group by or grouping functions (count (), min ()......), Merge having and where.
For each table in the join, construct a simpler where to perform where calculation on the table faster and skip the record as soon as possible.
Tables with all constants are read out before query. Constant table:
Empty table or table with only one row.
For a table that is used together with the where clause of a primary key or unique index, all the indexes here use constant expressions and the index part is defined as not null.
All of the following tables are used as constant tables:

Mysql> select * from t where primary_key = 1; mysql> select * from t1, t2 where t1.primary _ key = 1 and t2.primary _ key = t1.id; try all possibilities to find the best join combination for table join. If all columns in order by and group by come from the same table, the table is selected first when joined.
If an order by clause and a different group by clause exist, or if order by or group by contains columns from other tables in the first table in the join queue, a temporary table is created.
If SQL _small_result is used, mysql uses a temporary table in memory.
The index of each table is queried and the best index is used unless the optimizer deems that table scanning is more effective. Whether a scan is used depends on whether the best index spans more than 30% of tables. The optimizer is more complex and its estimation is based on other factors, such as the table size, number of rows, and I/o BLOCK SIZE. Therefore, the fixed proportion does not determine whether to use the index or scan.
In some cases, mysql can read data from indexes, or even not query data files. If all the columns used by the index are numerical classes, only the index tree is used for query.
The rows that do not match the having clause are skipped before each record is output.
Below are some examples of quick queries:

Select count (*) from tbl_name; select min (key_part1), max (key_part1) from tbl_name; select max (key_part2) from tbl_name where key_part1 = constant; select... from tbl_name order by key_part1, key_part2 ,... limit 10; select... from tbl_name order by key_part1 desc, key_part2 desc ,... limit 10; only the index tree can be used for the following queries (assuming that the index column is Numeric ):

Select key_part1, key_part2 from tbl_name where key_part1 = val; select count (*) from tbl_name where key_part1 = val1 and key_part2 = val2; select key_part2 from tbl_name group by key_part1; the following queries use indexes to retrieve rows in the order of sorting, without sorting them separately:

Select... from tbl_name order by key_part1, key_part2,...; select... from tbl_name order by key_part1 desc, key_part2 desc,...; 7.2.5. Range Optimization
7.2.5.1. Single-element index range access method
7.2.5.2. Multi-element index range access method
The range access method uses a single index to search for a subset of table records within one or more index values. It can be used for single-part or multi-element indexing. The following sections describe in detail how to extract ranges from where clauses.

7.2.5.1. Single-element index range access method
For a single-element index, you can use the corresponding conditions in the where clause to easily represent the index value range. Therefore, we call it a range condition rather than a "range ".

The single-element index range conditions are defined as follows:

· For B-tree and hash indexes, when the =, <=>, in, is null, or is not null operator is used, the comparison between key elements and constant values corresponds to a range condition.

· For btree indexes, when using >,<>=, <=, between, and ,! = Or <>, or like 'pattern' (where 'pattern' does not start with a wildcard) operator, the comparison between the key element and the constant value corresponds to a range condition.

· For all types of indexes, a Range condition is generated when multiple range conditions are combined with or and.

The "Constant Value" described above refers:

· Query constants in a string

· Columns in the const or system table in the same join

· Results of non-correlated subqueries

· An expression completely composed of subexpressions of the previous type

The following are examples of queries with range conditions in the where clause:

Select * from t1 where key_col> 1 and key_col <10; select * from t1 where key_col = 1 or key_col in (15,18, 20 ); select * from t1 where key_col like 'AB %' or key_col between 'bar' and 'foo'; note that the extraordinary values can be converted to constants in the constant propagation phase.

Mysql tries to extract range conditions from the where clause for each possible index. In the extraction process, conditions that cannot be used to form a range condition are discarded, conditions that overlap ranges are combined, and conditions that generate a null range are deleted.

For example, consider the following statement where key1 is an indexed column and nonkey has no index:

Select * from t1 where (key1 <'abc' and (key1 like 'abcde % 'or key1 like' % B ') or (key1 <'bar' and nonkey = 4) or (key1 <'uux 'and key1> 'Z'); The key1 extraction process is as follows:

1. Start with the original where clause:

2. (key1 <'abc' and (key1 like 'abcde % 'or key1 like' % B ') or

3. (key1 <'bar' and nonkey = 4) or

4. (key1 <'uux 'and key1> 'Z ')

5. Delete nonkey = 4 and key1 like '% B' because they cannot be used for range scanning. The correct way to delete them is to replace them with true so that matching records are not lost during a range scan. After they are replaced with true, You can get:

6. (key1 <'abc' and (key1 like 'abcde % 'or true) or7. (key1 <'bar' and true) or8. (key1 <'uux 'and key1> 'Z') 9. to cancel a condition that is always true or false:

· (Key1 like 'abcde % 'or true) always true

· (Key1 <'uux 'and key1> 'Z') always false

Replace the following conditions with constants:

(Key1 <'abc' and true) or (key1 <'bar' and true) or (false) Delete unnecessary true and false constants. We get

(Key1 <'abc') or (key1 <'bar') 10. Combine the overlapping ranges into a final condition for generating range scans:

11. (key1 <'bar') in general (as described in the previous example), the conditions used for range scanning are less restrictive than the where clause. Mysql then performs a check to filter out the rows that meet the range condition but do not fully meet the where clause.

The range condition extraction algorithm can process nested and/or structures in any depth, and its output does not depend on the order in where clause