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

This article will explain in detail how to optimize query sentences in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

30 kinds of SQL query sentence Optimization methods commonly used in MySQL

1. Try to avoid using the! = or operator in the where clause, otherwise the engine will give up using the index and do a full table scan.

2. In order to optimize the query, we should avoid full table scanning as far as possible, and first consider establishing indexes on the columns involved in where and order by.

3. Try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where num is null

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

4. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

Select id from t where num=10 or num=20

You can query it like this:

Select id from t where num=10

Union all

Select id from t where num=20

5. The following query will also cause a full table scan: (no leading percent sign)

Select id from t where name like'% c%'

Let's go to the index.

Select id from t where name like'c%'

To improve efficiency, consider full-text retrieval.

6. In and not in should also be used with caution, otherwise it will cause full table scanning, such as:

Select id from t where num in (1, 2, 3)

For consecutive values, use between instead of in:

Select id from t where num between 1 and 3

7. If you use parameters in the where clause, it will also cause a full table scan. Because SQL parses local variables only at run time, the optimizer cannot defer the choice of an access plan until run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and cannot be used as an input to the index selection. A full table scan will be performed as follows:

Select id from t where num=@num

You can force the query to use the index instead:

Select id from t with (index (index name)) where num=@num

8. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

9. Try to avoid functional operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where substring (name,1,3) = 'abc'-name id that begins with abc

Select id from t where datediff (day,createdate,'2005-11-30') = 0-'2005-11-30' generated id

It should be changed to:

Select id from t where name like 'abc%'

Select id from t where createdate > = '2005-11-30' and createdate

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.