But please note that the following optimization is not complete.MYSQL has implemented many optimizations, but I don’t have time to test them all.
Some of MySQL’s optimizations 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)
Constant transfer:
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
Remove constant conditions:
(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.
There is no WHERE COUNT(*) on a single table to retrieve information directly from the table.When using only one table, do the same for any NOT NULL expressions.
Early detection of invalid constant expressions.MySQL quickly detects that certain SELECT statements are impossible and do not return rows.
If you do not use GROUP BY or grouping functions (COUNT(), MIN()...), HAVING and WHERE are combined.
For each sub join (sub join), construct a simpler WHERE to get a faster WHERE calculation and also skip records as soon as possible.
All constant tables are read before any other tables in the query.
A constant table is:
An empty table or a table with 1 row.
A table used with the WHERE clause of a UNIQUE index or a PRIMARY KEY, where all index parts use a constant expression and the index part is defined as NOT NULL.
All 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;
The best join combination for joined tables is found by trying all possibilities:(.If all columns in ORDER BY and GROUP BY come from the same table, then when joining, the table It is selected first.
If you use SQL_SMALL_RESULT, MySQL will use an in-memory table.
If there is an ORDER BY clause and a different GROUP BY clause, or if ORDER BY or GROUP BY contains not From the columns of the other tables in the first table in the join queue, create a temporary table.
Because DISTINCT is transformed into a GROUP BY on all columns, the combination of DISTINCT and ORDER BY will also be required in many cases A temporary table.
The index of each table is queried and an index spanning less than 30% of the rows is used.If such an index is not found, a fast table scan will be used.
In some cases , MySQL can read rows from the index without even querying the data file.If all the columns used by the index are numeric, then only the index tree is used to answer the query.
Before each record is output, those that do not match HAVING The clause rows will be skipped.
Here are some quick query examples:
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&g t; SELECT...FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,...LIMIT 10;
The following query can be solved using only an 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 the index to search in sorted order, without another sort:
mysql> SELECT...FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT...FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...