Master experience: Ways to improve MySQL performance (1) (2) _php tutorial

5. Not

We often use logical expressions in the WHERE clause when querying, such as greater than, less than, equal to, and not equal to, and can also use and (with), or (or), and not (non). Not can be used to negate any logical operation symbol. The following is an example of a NOT clause:

... where not (status = ' VALID ')

If you want to use not, you should precede the phrase with parentheses and precede the phrase with the NOT operator. The NOT operator is included in another logical operator, which is the not equal to (<>;) operator. In other words, the not is still in the operator, even if the not word is not explicitly added to the query where clause, see the following example:

.. where status <>; ' INVALID '

Let's look at the following example:

SELECT * FROM Employee where salary<>;3000;

For this query, it can be rewritten to not use not:

SELECT * FROM employee where salary<3000 or salary>;3000;

Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second query allows Oracle to use indexes on salary columns, while the first query cannot use indexes.

6. In and Exists

Sometimes a column is compared to a series of values. The simplest approach is to use subqueries in the WHERE clause. You can use a two-format subquery in the WHERE clause.

The first format is the use of the in operator:

... where column in (SELECT * from ...);

The second format is the use of the exist operator:

... where exists (select ' X ' from ...);

I believe most people will use the first format because it is easier to write, whereas the second format is much more efficient than the first. In Oracle, you can overwrite almost all in-operator queries to subqueries that use exists.

In the second format, the subquery begins with ' select ' X '. Use the EXISTS clause no matter what data the subquery extracts from the table it only looks at the WHERE clause. This way, the optimizer does not have to traverse the entire table and can do its work only by indexing (this assumes that the column used in the WHERE statement has an index). As opposed to the in clause, exists uses a concatenated subquery, which is more difficult to construct than in subqueries.

http://www.bkjia.com/PHPjc/631005.html www.bkjia.com true http://www.bkjia.com/PHPjc/631005.html techarticle 5. Not we often use some logical expressions in the WHERE clause when querying, such as greater than, less than, equal to, and not equal to, and can also use and (with), or (or), and not (non ...) .

