How MySQL optimizes WHERE clause

mysql| optimization where optimization is mainly in select, because they are used most mainly in there, but the same optimizations can also be used for delete and UPDATE statements.

However, please note that the following optimizations are not complete. MySQL has implemented many optimizations, but I don't have time to test them all.

Some of the optimizations for MySQL are listed below:

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 transfer into:

(A-> b>5 and B=c and a=5

To delete a 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.

The Count (*) of no where on a single table retrieves information directly from the table. You do this for any not NULL expression when you are using only one table.

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 () ...), a having is merged with where.

For each sub join, construct a simpler where to get a faster where calculation and skip the record as quickly as possible.

Tables of all constants are read before any other table in the query.

A table of constants is:

An empty table or a table with 1 rows.

A table used in conjunction with a unique index, or a WHERE clause of a primary key, where all the index parts use a constant expression 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;

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

If you use Sql_small_result,mysql will use a table in memory.

Create a temporary table if you have an ORDER BY clause and a different GROUP BY clause, or if the order by or group by contains columns that are not from the first table in the join queue.

Because distinct is transformed to a group by,distinct on all columns and the order by combination will also require a temporary table in many cases.

The index of each table is queried and uses an index that spans fewer than 30% rows. If such an index is not found, a quick table scan is used.

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 numbers, then only the index tree is used to answer the query.

The rows that do not match the HAVING clause are skipped before each record is exported.

Here are some quick examples of 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 query can be resolved using only 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 the index to retrieve in sort order without an additional sort:

Mysql> SELECT ... From Tbl_name to Key_part1,key_part2,...
Mysql> SELECT ... From Tbl_name to Key_part1 desc,key_part2 DESC,...