Shulou(Shulou.com)05/31 Report--

This article mainly shows you "MySQL how to optimize the WHERE clause", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL how to optimize the WHERE clause" this article.

How MySQL optimizes the WHERE clause (turn) [@ more@] where optimizations are mainly used in SELECT because they are mainly used there, but the same optimizations can be used in DELETE and UPDATE statements.

Note, however, that the following optimizations are not complete. MYSQL implemented a lot of optimizations, but I didn't have time to test them all.

Some optimizations for MySQL are listed below:

Remove unnecessary parentheses:

((an AND b) AND c OR ((an AND b) AND (c AND d)

-> (an AND b AND c) OR (an AND b AND c AND d)

Constant call:

(a)

-> b > 5 AND baccalaurec AND astat5

Delete constant condition:

(B > = 5 AND AND 5) OR (Blood6 AND 5) OR (Bath7 AND 5)

-> Battle5 OR Bron6

The constant expression used by the index is evaluated only once.

COUNT (*) without a WHERE on a single table retrieves information directly from the table. When using only one table, do the same for any NOT NULL expression.

Early detection of invalid constant expressions. It is not possible for MySQL to quickly detect certain SELECT statements and does not return rows.

If you don't use GROUP BY or grouping functions (COUNT (), MIN ()...) HAVING merged with WHERE.

For each subjoin (sub join), construct a simpler WHERE to get a faster WHERE calculation and skip records as soon as possible.

The table of all constants is read out before any other table in the query.

A table of constants is:

An empty table or a table with 1 row.

A table used with an UNIQUE index, or a WHERE clause of 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 WHERE t1.primary_key=1 AND t2.primary_key=t1.id T2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id

The best join combination for a join table is to try all the possibilities to find: (. If all the 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, you 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 ORDER BY or GROUP BY contains columns that are not from other tables in the join queue.

Because DISTINCT is transformed to a combination of GROUP BY,DISTINCT and ORDER BY on all columns, a temporary table will also be required in many cases.

The index of each table is queried and uses an index that spans less than 30% of the rows. If such an index cannot be found, a fast table scan will be used.

In some cases, MySQL can read rows from an index without even querying data files. 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 output.

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 solved using only the index tree (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 an index, 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,...

The above is all the content of the article "how MySQL optimizes the WHERE clause". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.