Optimizing the speed of MySQL select query

In general, to make a slower select ... where it is faster, you should first check to see if you can add an index. References between different tables are usually done by index. You can use the explain statement to determine which indexes the SELECT statement uses. See 7.4.5, "How the MySQL tutorial uses indexes" and the 7.2.1 section, "Explain syntax (get information about Select)".

Here are some general suggestions for speeding up queries on the MyISAM table:

· To help MySQL better optimize the query, run Analyze table or Myisamchk--analyze on a table that is loaded with data. This updates the value of the average number of rows that have the same value for each index (of course, if there is only one index, this is always 1.) MySQL uses this method to determine which index to choose when you join two of tables based on the expression of a very great quantity. You can use the show index from Tbl_name and check the cardinality value to check the results of the table analysis. Myisamchk--description--verbose can display index distribution information.

· To sort an index and data based on an index, use Myisamchk--sort-index--sort-records=1 (if you want to sort on index 1). If you have only one index, and you want to read all the records based on the order of the index, this is a good way to make your query faster. Note, however, that the first time a large table is sorted in this way it will take a long time!

7.2.4. mysql how to optimize WHERE clause
This section discusses optimizations that are made to handle a WHERE clause. The SELECT statement is used in the example, but the same optimizations apply to the WHERE clause in the DELETE and UPDATE statements.

Please note that the work on the MySQL optimizer is ongoing, so the section is not perfect. MySQL performs a number of optimizations that are not detailed in this article.

The following is a list of some of the optimizations that MySQL performs:

· To 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) · Constant overlap:

·                (A<b and B=c) and a=5         -> b>5 and B=c and a=5 To 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 evaluated only once.

For MyISAM and heap tables, the COUNT (*) of no where on a single table retrieves information directly from the table. This is also done for a NOT NULL expression when only one table is used.
An early detection of an invalid constant expression. MySQL quickly detects that some SELECT statements are not possible and do not return rows.
If you do not use the group by or grouping functions (count (), min () ...), having is merged with where.
For each table in the join, construct a simpler where to calculate the table and skip the record as quickly as possible.
Tables for all constants are read first in the query compared to the other tables. The Constant table is:
An empty table or a table with only 1 rows.
A table used in conjunction with a WHERE clause in a primary key or a unique index, where all the index parts use constant expressions and the index portion is defined as NOT NULL.
All of the following tables are used as constants 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 can find the best join combinations for table joins when trying all possibilities. If all the columns in order by and group by come from the same table, the table is first selected when joins. The
creates a temporary table if there is an ORDER BY clause and a different GROUP BY clause, or if order by or group by contains columns from a table other than the first table in the join queue.
If you use Sql_small_result,mysql to use a temporary table in memory. The
indexes for each table are queried and the best indexes are used, unless the optimizer considers it more efficient to use table scans. Whether to use scans depends on whether the best indexes span more than 30% of the tables. The optimizer is more complex, estimated based on other factors, such as table size, number of rows, and I/O block size, so fixed proportions no longer determine whether to choose to use an index or a scan.
In some cases, MySQL can read the trip from the index, even without querying the data file. If all the columns used by the index are numeric classes, then only the index tree is used for querying.
The row that does not match the HAVING clause is skipped before each record is output.
Here 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-key_part1,key_part2,... limit 10; Select ... from tbl_name ORDER by key_part1 Desc, key_part2 desc, ... limit 10; The following query can be resolved using only the index tree (assuming the indexed 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 query uses the index to retrieve rows in sorted order without additional sorting:

Select ... from tbl_name-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. A range access method for a single element index
7.2.5.2. A range access method for multi-element indexing
The range access method uses a single index to search for a subset of table records that are contained within one or several index-value distances. Can be used for single-part or multiple-element indexes. The following sections describe in detail how to extract intervals from the WHERE clause.

7.2.5.1. A range access method for a single element index
For a single element index, you can easily represent an index value interval with the corresponding condition in the WHERE clause, so we call the range condition instead of the interval.

The single element index range condition is defined as follows:

· For Btree and hash indexes, the comparison of a key element to a constant value corresponds to a range condition when using the =, <=>, in, is null, or the is not NULL operator.

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

· For all types of indexes, multiple range conditions combine or OR and produce a range condition.

The "constant value" described previously refers to:

· Constants in the query string

· Const in the same join or columns in the system table

· Results of no associated subquery

· An expression that consists entirely of a subexpression of a previous type

The following is an example of a query with a range condition in a 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 in the constant propagation phase, some of the extraordinary measures can be converted to constants.

MySQL attempts to extract a range condition 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 produce overlapping ranges are grouped together, and conditions that produce empty scopes are deleted.

For example, consider the following statement, where Key1 is an indexed column and nonkey does not have an 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 remove them is to replace them with true so that matching records are not lost when a range scan is performed. After replacing them 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. Cancel a condition that is always true or false:

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

· (Key1 < ' Uux ' and key1 > ' z ') always False

Replacing these conditions with constants, we get:

(Key1 < ' abc ' and True) or (Key1 < ' bar ' and true) or (false) to remove unnecessary true and false constants, we get

(Key1 < ' abc ') or (Key1 < ' bar ') 10. Combine overlapping intervals into a final condition that results in a range scan:

(Key1 < ' bar ') overall (as described in the previous example), the conditions used for range scanning are less restrictive than where clauses. MySQL then performs a check to filter out rows that meet the range criteria but do not fully satisfy the WHERE clause.

The range condition extraction algorithm can handle the and/or structure of any depth nested, and its output does not depend on the order in which the condition appears in the WHERE clause