MYSQL WHERE statement optimization

Note, however, that the following optimization is not complete. MYSQL implemented many optimizations, but I didn't have time to test them all.
Some optimizations of 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)
Constant adjustment:
(a < b AND b=c) AND a=5
- > b > 5 AND b=c AND a=5
Delete constant condition:
(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.
COUNT(*) on a single table without an WHERE retrieves information directly from the table. Do the same for any NOT NULL expression when only one table is used.
Early detection of invalid constant expressions. It is impossible for MySQL to quickly detect certain SELECT statements and not return rows.
If you do not use GROUP BY or group functions (COUNT(), MIN()...) , HAVING merged with WHERE.
For each subjoin (sub join), construct a simpler WHERE to get a faster WHERE calculation and also skip the record as quickly as possible.
All constant tables in the query are read before any other tables.
The table of 1 constant is:
1 empty table or 1 table with 1 row.
Compared to tables used in 1 UNIQUE index, or 1 PRIMARY KEY WHERE clause 1, here all the index parts use 1 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 combination of joins for join tables is found by trying all possibilities :(. If all the columns in ORDER BY and GROUP BY are from the same table, the table is selected first when joined.
If you use SQL_SMALL_RESULT, MySQL will use 1 table in memory.
Create a temporary table if you have an ORDER BY clause and a different GROUP BY clause, or if ORDER BY or GROUP BY contain columns from other tables that are not from the first table in the join queue.
Because DISTINCT is transformed to 1 GROUP BY on all columns, DISTINCT combined with ORDER BY will also require a temporary table in many cases.
The indexes of each table are queried and use indexes that span less than 30% of rows. If such an index cannot be found, a quick table scan is used.
In some cases, MySQL can read the trip from the index without even querying the data file. If all the columns used by the index are numeric, only the index tree is used to answer the query.
Lines that do not match the HAVING clause are skipped before each record is printed.
Here are some quick 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 be resolved using the index tree alone (assuming 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 queries are retrieved in sort order using the index instead of a separate 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,...