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

How to optimize the speed of database queries, for this problem, this article details the corresponding analysis and solutions, hoping to help more small partners who want to solve this problem find a simpler and easier way.

For database query speed optimization, what are the methods for optimizing database speed? You can try the optimization tips that Xiaobian brings to you.

1. Optimize the query and avoid full table scanning as much as possible.

First, consider indexing where and orderby columns.

Let's compare 177 entries in a table, a full table scan versus an index.

From these two ways to query the database results, query speed after the establishment of the index to improve some, the amount of data is not obvious now, if there are 100,000 speed table, the difference will be obvious.

2. Minimize global scanning of tables when writing data statements (1) Reduce null judgment of where field value

SELECT*FROM"tb_real_time_car"wherepay_status=null

How to do this causes the engine to abandon the index and do a full table scan

This should be set (i.e., we automatically default to an o value when we save the database, instead of writing nothing):

SELECT*FROM"tb_real_time_car"wherepay_status=0

(2) Avoid using!= in where clauses as much as possible. or operator

SELECT*FROM"tb_real_time_car"wherepay_status!= null;//or SELECT*FROM"tb_real_time_car"wherepay_status>

Such a write would cause the engine to discard the index and do a full table scan.

(3) Avoid using or in the where clause to connect conditions

SELECT*FROM"tb_real_time_car"wherepay_status!= nullorenter_time=null;

This will cause the engine to abandon the index and do a full table scan

You can do this:

SELECT*FROM"tb_real_time_car"wherepay_status!= nullunionallSELECT*FROM"tb_real_time_car"whereenter_time=null;

(4) In and notin should also be used with caution

SELECT*FROM"tb_real_time_car" wherehowedin [1,2,3,4];//OR SELECT*FROM"tb_real_time_car" wherehowednotin [1,2,3,4];

This will also result in a full table scan.

It can be written like this:

SELECT*FROM"tb_real_time_car"whererowedbetween1and5;

(5) Less use of fuzzy matching like

SELECT*FROM"tb_real_time_car"whereenter_timelike'%2016-09-01%'

(6) Expression operations on fields in where clauses should be avoided as much as possible

SELECT*FROM"tb_real_time_car"whererowid/4=100;

This will cause the engine to abandon the index and do a full table scan

It should read:

SELECT*FROM"tb_real_time_car"whererowid=4*100;

(7) Do not use * wildcards anywhere to query all

SELECT*FROM"tb_real_time_car"whererowid/4=100;

Query all data with wildcard *, which is also very time-consuming, we should query what fields we need.

This should be done:

SELECTleave_timeFROM"tb_real_time_car"whererowid/4=100;

3. Do not perform arithmetic operations when judging conditions

SELECT*FROM"tb_real_time_car"whererowid/4=100;

So do not perform functions, arithmetic operations, or other expression operations to the left of the "=" in the where clause, and the system may not use the index correctly

This should be done:

SELECT*FROM"tb_real_time_car"whererowed=400;

4, many times with exists instead of in is a good choice

SELECT*FROM"tb_real_time_car"whererowed(selectrowedfrom"tb_real");

It should be written like this:

SELECT*FROM"tb_real_time_car"whereexists(selectrowedfrom"tb_real"whererowed=tb_real.rowid);

About the speed of database query optimization is how to answer the question to share here, I hope the above content can have some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.