Mysql limit Speed Optimization Analysis

Mysql tutorial database tutorial optimization is very important. Limit is the most commonly used and optimized. Mysql limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

10 data records are also retrieved.

 

Select * from yanxue8_visit limit, 10 select * from yanxue8_visit limit 0, 10 is not an order of magnitude.

There are also many five limit optimization guidelines on the Internet, which are translated from the mysql manual. They are correct but not practical. Today I found an article about limit optimization, which is quite good.

Instead of using limit directly, we first get the offset id and then use limit size to get data. Based on his data, it is much better to use limit directly. Here I use data in two cases for testing. (Test environment: win2033 + p4 dual-core (3 ghz) + 4G memory mysqllimit query)

1. When offset is small

 

1. select * from yanxue8_visit limit for 10 or 10 times, and keep the time between 0.0004 and 0.0005.

 

Select * from yanxue8_visit where vid> = (select vid from yanxue8_visit order by vid limit 10, 1) limit more than 10 times, with the time between 0.0005 and 0.0006, mainly 0.0006

Conclusion: When the offset is small, it is better to use limit directly. This is obviously the cause of subquery.

2. When the offset value is large

 

Select * from yanxue8_visit limit, run for more than 10 times, and keep the time around 0.0187

 

Select * from yanxue8_visit where vid> = (select vid from yanxue8_visit order by vid limit, 1) limit is run more than 10 times, and the time is kept at around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.

Pay attention to correct your limit statement and optimize mysql later.

Recommender comment

Mysql optimization is very important. The other most commonly used and most needed optimization is limit. Mysql limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

To use a mysql database, you must use the limit clause in mysql to return the first few or some rows of data in the middle. We usually use limit to implement paging. The syntax is as follows:
SQL code
Select * from table limit [offset,] rows | rows offset

Select * from table limit [offset,] rows | rows offset

The limit clause is used to force the select statement to return the specified number of records. Limit accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. Note: the offset of the initial record row is 0 rather than 1.
Example:
SQL code
Select * from table limit 5, 10; // retrieves records from 6 to 15 rows.

Select * from table limit 5, 10; // retrieves records from 6 to 15 rows.

To retrieve all record rows from an offset to the end of the record set, you can specify the second parameter-1:
SQL code
Select * from table limit 75,-1; // retrieve record row 75-last.

Select * from table limit 75,-1; // retrieve record row 75-last.

If only one parameter is specified, it indicates the maximum number of record rows returned:
SQL code
// Retrieve the first five record rows. In other words, limit n is equivalent to limit 0, n
Select * from table limit 5;

 

Mysql does not allow subqueries to use limit.

SQL = "select * from users" + PS tutorial ql + "limit" + (pcurrentpage_int-1) * getpagesize () + "," + getpagesize ();
This is my original SQL statement.
When the data is big, the performance is not very good.
I will rewrite it.
This way

SQL = "select * from users" + psql;
If (psql. length () = 0 ){
SQL + = "where id> = (select id from users order by id limit" + (pcurrentpage_int-1) * getpagesize () + ", 1) limit" + getpagesize ();
} Else {
SQL + = "and id> = (select id from users" + psql + "order by id limit" + (pcurrentpage_int-1) * getpagesize () + ", 1) limit "+ getpagesize ();
}

 

I have also encountered limit performance problems, but the performance bottleneck is on order. The order by operation on * is very slow. My improvement is to only do id, find the required id and then select * from table where id in (idstring); performance is improved a lot. For paging, you can use SQL _calc_found_row.

Select SQL _calc_found_row id from table where ------ limit 0, 50;
Select found_row ();
The two statements can be used together to find out the total number of qualified results of the first statement.