Tips: How to improve MySQL Performance (1) (2) _ PHP Tutorial

Tips: How to improve MySQL Performance (1) (2 ). 5. NOT we often use some logical expressions in the where clause during queries, such as greater than, less than, equal to, and NOT equal to. we can also use and (and), or (or) and not (not 5. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, and not equal to. we can also use and (and), or (or) and not (not ). NOT can be used to reverse all logical operators. The following is an example of a NOT clause:

... Where not (status = 'valid ')

If you want to use NOT, brackets should be added before the phrase to be reversed, and the NOT operator should be added before the phrase. NOT operator is included in another logical operator, which is NOT equal to (<>;) operator. In other words, even if the NOT word is NOT explicitly added to the query where clause, NOT is still in the operator. See the following example:

... Where status <>; 'invalid'

Let's look at the example below:

Select * from employee where salary <>; 3000;

You can rewrite this query to NOT using NOT:

Select * from employee where salary <3000 or salary>; 3000;

Although the results of these two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes for salary columns, while the first query does not.

6. IN and EXISTS

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. Subqueries in two formats 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 is to use the EXIST operator:

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

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

In the second format, the subquery starts with 'select. Use the EXISTS clause to query the data extracted from the table without a pipe. it only displays the where clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where statement has an index ). Compared with the IN clause, EXISTS uses connected subqueries, which is 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 during queries, such as greater than, less than, equal to, and NOT equal to. we can also use and (and), or (or) and not (not...