MySQL query performance optimization (1)

Use the EXPLAIN statement to check SQL statements

When you put the keyword "EXPLAIN" in front of a SELECT statement, MySQL explains how it will process the SELECT statement and provides information about how the table is joined and in what order.

With the help of EXPLAIN, you can know when you must add an index to the table to obtain a faster SELECT statement that uses the index to locate the record.

EXPLAIN tbl_name

Or explain select select_options
EXPLAIN tbl_nameYesDESCRIBE tbl_name or show columns from tbl_name.

The output from EXPLAIN includes the following columns:

· Table
The table referenced by the output row.

· Type
Join type. Various types of information are provided below.
Different join types are listed below in the best to worst type order:
System const eq_ref ref range index ALL possible_keys

· Key
The key column displays the keys actually determined by MySQL. If no index is selected, the key is NULL.

· Key_len
The key_len column displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that MySQL will actually use multiple key values.

· Ref
The column ref shows which column or constant is used together with the key to select rows from the table.

· Rows
The rows column shows the number of rows that MySQL believes must be checked for query.

· Extra
If the Extra column contains the text Only index, this means that the information is Only retrieved from the index tree. Generally, this is faster than scanning the entire table. If the Extra column contains the text where used, it means that a WHERE clause will be used to limit which rows match or send to the customer in the next table.
By multiplying all the values of the rows output by EXPLAIN, you can get a prompt about how good a join is. This should roughly tell you how many lines MySQL must check to execute the query.

For example, the following full connection:

Mysql> explain select student. name From student, pet
-> WHERE student. name = pet. owner;
The conclusion is:
+ --------- + ------ + ------------- + ------ + --------- + ------ + ------------ +
| Table | type | possible_keys | key | key_len | ref | rows | Extra |
+ --------- + ------ + ------------- + ------ + --------- + ------ + ------------ +
| Student | ALL | NULL | 13 |
| Pet | ALL | NULL | 9 | where used |
+ --------- + ------ + ------------- + ------ + --------- + ------ + ------------ +

SELECT query speed

In general, when you want to make a slow SELECT... WHERE faster, the first thing to check is whether you can add an index. All references between different tables must be indexed. You can use EXPLAIN to determine which index is used for a SELECT statement.

Some general suggestions:
· To help MySQL optimize queries, run myisamchk -- analyze on a table after it has loaded the relevant data. This indicates the average number of rows with the same value for each updated value. Of course, this is always 1 for the unique index .)
· To sort an index and data according to an index, use myisamchk -- sort-index -- sort-records = 1 if you want to sort it on index 1 ). If you have a unique index, you want to read all records in the order of the index location, which is a good way to make it faster. However, note that this sorting is not best written, and it will take a long time for a large table!

How does MySQL optimize the WHERE clause?

The where optimization is put in the SELECT statement, because they are mainly used there, but the same optimization is used for DELETE and UPDATE statements.

Note that this section is incomplete. MySQL has indeed made many optimizations and we don't have time to record them all.

Some optimizations implemented by MySQL are listed below:

1. Delete 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)

2. Constant call:
(->B> 5 AND B = c AND a = 5

3. Delete the constant condition (required for constant transfer ):
(B> = 5 and B = 5) OR (B = 6 AND 5 = 5) OR (B = 7 AND 5 = 6)
-> B = 5 or B = 6

4. The constant expression used by the index is calculated only once.

5. No where count (*) in a single table is directly retrieved from the table. This is also true for any not null expression when only one table is used.

6. Early Detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and no rows are returned.

7. If you do not use group by or grouping functions (COUNT (), MIN ()......), Merge HAVING and WHERE.

8. For each subjoin, construct a simpler WHERE to get a faster WHERE computation and skip the record as soon as possible.

9. All constant tables are read before any other tables in the query. A constant table is:

· An empty table or a table with one row.

· For a table used together with a UNIQUE index or a WHERE clause of a primary key, all the indexes here use a constant expression and the index 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,t2WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

10. The best join combination for the joined table is found by trying all possibilities :(. If all columns in order by and group by come from the same table, the table is selected first when the table is clean.

11. If there is an order by clause and a different group by clause, or if order by or group by contains columns not from the first table in the join queue, create a temporary table.

12. If you use SQL _SMALL_RESULT, MySQL uses a table in the memory.

13. Because DISTINCT is converted to a group by clause on all columns, combining DISTINCT with order by also requires a temporary table in many cases.

14. The index of each table is queried and indexes that span less than 30% rows are used. If such an index cannot be found, use a quick table scan.

15. In some cases, MySQL can read data from indexes, or even not consult data files. If the index uses numbers for all columns, only the index tree is used to answer queries.

16. Before each record is output, the rows that do not match the HAVING clause are skipped.

Below are some examples of quick queries

mysql> SELECT COUNT(*) FROM tbl_name;mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;mysql> SELECT MAX(key_part2) FROM tbl_name WHERE key_part_1=constant;mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10;mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

The following queries can only be solved using the index tree (assuming that the index column is Numeric ):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;mysql> SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2;mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following query uses an index for sorting and does not use another sorting:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...