Expert's experience: Methods to improve MySQL performance (1) (2)_PHP tutorial

5. NOT

We often use some logical expressions in the where clause when querying, such as greater than, less than, equal to, not equal to, etc. You can also use and ( and), or (or) and not (not). NOT can be used to negate any logical operation sign. Here is an example of a NOT clause:

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

If you want to use NOT, you should put parentheses before the negated phrase, And prepend the NOT operator to the phrase. The NOT operator is contained within another logical operator, which is the not equal to (;) operator. In other words, even if the NOT word is not explicitly added to the where clause of the query, NOT is still in the operator, see the following example:

... where status ;'INVALID'

Look at the following example again:

select * from employee where salary;3000;

For this query, you can rewrite it without using NOT:

select * from employee where salary;3000;

Although the results of these two queries are the same, the second query plan will be faster than the first query plan. The second query allows Oracle to use indexes on the salary column, while the first query cannot use indexes.

6. IN and EXISTS

Sometimes a column is compared to a series of values. The simplest way is to use a subquery in the where clause. Two formats of subqueries can be used in the where clause.

The first format is to use the IN operator:

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

The second format The format is to use the EXIST operator:

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

I believe that most people will use the first one format, because it is easier to write, and the second format is actually far more efficient than the first format. In Oracle, almost all IN operator subqueries can be rewritten as subqueries using EXISTS.

In the second format, the subquery starts with 'select 'X'. Using the EXISTS clause no matter what data the subquery extracts from the table, it only looks at the where clause. In this way, the optimizer does not have to traverse the entire table and can complete the work based on the index only (this assumes that the column used in the where statement has an index). Compared with the IN clause, EXISTS uses connected subqueries, which are more difficult to construct than IN subqueries.

http://www.bkjia.com/PHPjc/631005.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631005.htmlTechArticle5. 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 etc., you can also use and (and), or (or) and not (not...