A Guide on WHERE Clause Optimization in MySQL

The most important optimization in WHERE is in the SELECT – It just because that it works there. It can also be used for DELETE and UPDATE statements.

Moreover, the following optimization is just a part of that optimization. MYSQL has implemented a number of optimizations, but I didn’t test all of them here.

Some common optimization of MySQL are listed below:

1. You could try to remove those unnecessary parentheses, like:
((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)

and those transferred constants :
(A -> b> 5 AND b = c AND a = 5

Also, remove constants with conditions:
(B> = 5 AND B = 5) OR (B = 6 AND 5 = 5) OR (B = 7 AND 5 = 6)
-> B = 5 OR, B = 6

You set the constant expressions in Index can only be counted once.
Retrieve information directly from the table in a single table without a WHERE COUNT (*). It’d be the same for the NOT NULL expression when it’s using one table only.

And for those early detections of invalid constant expressions, your MySQL probably could not quickly detect those SELECT statements, and could not give you any feedback.
If you do not use GROUP BY or group functions (COUNT (), MIN () ……), the HAVING would merger with WHERE.

You then should construct a simpler WHERE calculation to skip records as soon as possible, and to join the sub files.

Please set all the constant tables are read first before any other tables in the query.

Read some more typical constant table below:
A table with zero rows, or with only one row.

A table that has been used with the WHERE clause on a UNIQUE index, or a PRIMARY KEY table, where all the Index parts of using a constant expression and the Index itself is defined as NOT NULL.
All the following tables are usually used as a constant table:

mysql> SELECT * FROM t the WHERE primary_key = 1;
mysql> SELECT * FROM t1, t2 WHERE t1.primary_key = 1 AND t2.primary_key = t1.id;

The best way to join all the tables is by trying to find all possibilities. If those columns in ORDER BY and/or GROUP BY come from the same table, that table would firstly selected when the tables are joined.

If you are using SQL_SMALL_RESULT, MySQL will then use a table in memory.
If there is an ORDER BY clause and other GROUP BY clause, then you can create a temporary table.

If the DISTINCT has been transformed into GROUP BY for all the columns, the DISTINCT and ORDER BY will also need a temporary table in many cases.

Please set the Index for each table is queried, and queries those across the line less than 30%. If this index is not available, you set it would run a quick table scan.
Besides, MySQL can read those columns from the index, even do not have to query the data file. If all the columns of the index used the digital format, then the only Index tree would be used to respond to those inquiries.
Before each record is output, set those rows that do not match the HAVING clause will be skipped.

Here are some quick query examples for you:

mysql> SELECT COUNT (*) FROM tbl_name The;
mysql> SELECT MIN (key_part1), MAX (key_part1) the FROM tbl_name The;
mysql> SELECT MAX (key_part2) FROM tbl_name The
WHERE key_part_1 = constant;
mysql> SELECT … FROM tbl_name The
The ORDER BY key_part1, key_part2, … the LIMIT 10;
mysql> SELECT … FROM tbl_name The
The ORDER BY key_part1 the DESC key_part2 the DESC … the LIMIT 10;

The following query uses only the index tree can be solved (assume that the indexed column is a number):

mysql> SELECT key_part1, key_part2 the FROM tbl_name The the WHERE key_part1 = val;
mysql> SELECT COUNT (*) FROM tbl_name The
The WHERE key_part1 = val1 AND key_part2 = val2;
mysql> SELECT key_part2 FROM tbl_name The the GROUP BY, key_part1;

And the following query uses the index in ordered-retrieval sorting once:

mysql> SELECT … FROM tbl_name The an ORDER BY key_part1, key_part2, …
mysql> SELECT … FROM tbl_name The key_part1 the DESC the ORDER BY, key_part2 DES

The Articles You Might Like

Recommended Download

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>