The where optimization is mainly in SELECT, because they are mainly used there, but the same optimization can also be used for DELETE and UPDATE statements. However, note that the following optimization is not complete. MYSQL has implemented many optimizations, but I don't have time to test them all. some optimizations of MySQL are listed below: delete unnecessary parentheses :( (aANDb) ANDcOR (aANDb) MySQL optimization
The where optimization is mainly in SELECT, because they are mainly used there, but the same optimization can also be used for DELETE and UPDATE statements.
However, note that the following optimization is not complete. MYSQL has implemented many optimizations, but I don't have time to test them all.
Some MySQL 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 call:
(
-> B> 5 AND B = c AND a = 5
Delete 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.
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.
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 subjoin, construct a simpler WHERE to get a faster WHERE computation and skip the record 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 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, 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, the table is selected first when joined.
If you use SQL _SMALL_RESULT, MySQL uses a table in memory.
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.
Because DISTINCT is transformed to a group by statement on all columns, combining DISTINCT with order by also requires a temporary table in many cases.
The index of each table is queried and indexes that span less than 30% rows are used. If such an index cannot be found, a quick table scan will be used.
In some cases, MySQL can read data from indexes, and does not even need to query data files. If the index uses numbers for all columns, only the index tree is used to answer queries.
Rows that do not match the HAVING clause are skipped before each record is output.
Below 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> 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 ,...
