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

Today, I will talk to you about how to optimize the WHERE clause of MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

MySQL optimizes WHERE clause

Where optimizations are mainly in SELECT, because they are mainly used there, but the same optimizations can also be used for 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,...

After reading the above, do you have any further understanding of how to optimize the WHERE clause for MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.