MySQL optimization WHERE clause

This section discusses the optimization of the WHERE clause, not only in select, the same optimizations are also tried with the WHERE clause in the DELETE and UPDATE statements;

1: Remove unnecessary parentheses:

 and  and OR  and  and  and d ))))  -  and  and OR  and  and  and

2: Constant Merge:

(A< b= and a=5 b>5  and b= and a=5

3: Constant Condition Removal:

(B>=5  andB=5)OR(B=6  and 5=5)OR(B=7  and 5=6) -B=5 ORB=6

4:INDEXES constant expressions are evaluated only once:

5: Single-table Count (*) without a Where condition directly retrieves statistics from the INFORMATION_SCHEMA library (for MyISAM and memory tables).

6: An earlier detection of invalid constant expressions. MySQL quickly discovers a where condition in the SELECT statement that cannot be established and returns no rows;

7: If the group BY or aggregate function (count (), Min (). Max ()) is not used, the HAVING clause is merged into the WHERE clause;

8: Each table that makes a link, a quick WHERE clause constructs the principle to skip more rows as much as possible;

9: In the query, all constant table is read before the other table, constant table is defined as follows:

1.1: An empty table or a table with only one row of data;

1.2: A table with a primary key or a unique index as a where conditional clause, and all index parts are compared to constant expressions, not null (a unique index may contain more than one null);

SELECT *  from WHERE Primary_key=1; SELECT *  from t1,t2   WHERE T1.primary_key=1 and T2.primary_key=t1.id;

10: By trying all the possibilities, find the best table join combination case, if all the columns in the ORDER BY clause and the GROUP BY clause come to the same table, then the table takes precedence when the join is read;

11: If the ORDER BY clause differs from the GROUP BY clause, or if the order by Or group by contains a column that is not in the first table of the query queue, a temporary table is created;

12: Optimizer If you think that using index is more efficient than a table scan, you will choose to use the Index,scan table if the best index requires a scan of more than 30% of the table records, but a fixed scale will not be determined using the table scan or index. The optimizer is more complex, depending on the extra factor, eg: the size of the table, the number of rows, the size of the block fast;

13: In some cases, MySQL can read the index data without having to return to data file (base table) (index overlay);

Here's a quick example:

SELECT COUNT(*) fromTbl_name;SELECT MIN(Key_part1),MAX(KEY_PART1) fromTbl_name;SELECT MAX(KEY_PART2) fromTbl_nameWHEREKey_part1=constant;SELECT... fromTbl_nameORDER  byKey_part1,key_part2,... LIMITTen;

tbl_name  key_part1key_part2DESC, ... LIMIT 10;

The following is MySQL querying index b-tree only, assuming that the index column value is numeric type:

SELECT  from WHERE Key_part1=val; SELECT COUNT (* from tbl_name  WHERE key_part1= and Key_part2 = Val2; SELECT  from GROUP  by Key_part1;

The following is the use of index to avoid filesort

SELECT  from tbl_name   ORDER  by Key_part1,key_part2,...; SELECT  from tbl_name   ORDER  by DESC DESC, ...;

MySQL optimization WHERE clause