Shulou(Shulou.com)06/01 Report--

This article will explain in detail about mysql to avoid full-screen scanning methods, Xiaobian think it is quite practical, so share it for everyone to make a reference, I hope you can gain something after reading this article.

MYSQL to avoid full table scanning methods: 1, where and order by involved in the column index;2, in the where clause to avoid null value judgment on the field, avoid using "!= "Or operator, avoid using or to connect conditions;3. Be careful with in and not in;4. Avoid using calculations on index columns.

MYSQL avoids full table scanning

1. To optimize queries, avoid full table scans as much as possible. First, consider building indexes on columns involved in where and order by.

2. Try to avoid making null value judgments on fields in where clauses, otherwise the engine will give up using indexes and perform full table scans.

For example: select id from t where num is null You can set the default value 0 on num, ensure that there is no null value in num column in the table, and then query like this: select id from t where num=0

3. Avoid using!= in where clauses Or operator, otherwise the engine forgoes using indexes in favor of a full table scan.

4. Avoid using or in the where clause to join conditions, otherwise it will cause the engine to abandon the index and do a full table scan (union can be used)

5.in and not in should also be used with caution, otherwise it will lead to full table scanning (if you can use between, don't use in)

6. The following query will also result in a full table scan.

select id from t where name like '% Lee %', select id from t where name like '% Lee '

To improve efficiency, you can use this format select id from t where name like 'Lee %', or consider full-text search.

7. Avoid calculations on indexed columns, that is, avoid expression and function operations on fields in where clauses, which will cause the engine to forgo indexes in favor of a full table scan.

For example: select id from t where num/2=100 should be changed to: select id from t where num=100*2

8. Many times using exists instead of in is a good choice: exists is used to check whether a subquery returns at least one row of data, and the subquery does not actually return any data, but returns a value of true or false.

select num from a where num in(select num from b)

Replace with the following statement: select num from a where exists (select 1 from b where num=a.num)

9. Do not use select from t anywhere, replace "" with a list of specific fields, and do not return any fields that are not needed.

10. Replace> with>=

Select * FROM EMP WHERE DEPTNO >=4

inefficiency: SELECT * FROM EMP WHERE DEPTNO >3

The difference between the two is that the former DBMS will jump directly to the first record with DEPT equal to 4, while the latter will first locate the record with DEPTNO=3 and scan forward to the first record with DEPT greater than 3.

11. Replace the having clause with the Where clause

About mysql to avoid full-screen scanning methods to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.