Optimization analysis of MySQL limit speed

The MySQL tutorial database tutorial optimization is quite important. The most common and most needed optimization is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.

It's also taking 10 data.

SELECT * FROM Yanxue8_visit limit 10000,10 select * from Yanxue8_visit limit 0,10 is not a quantitative level.

There are also a lot of five optimization guidelines on limit, which are translated from MySQL manual, although correct but not practical. Today I found an article to write about limit optimization, very good.

Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly. Here I use the data in two different situations to test. (Test environment WIN2033+P4 dual core (3GHz) +4g Memory mysqllimit query)

1, offset relatively small time

1.select * from Yanxue8_visit limit 10, 10 times run, time remains between 0.0004-0.0005

SELECT * from Yanxue8_visit where vid >= (select Vid-yanxue8_visit order by vid limit 10,1) limit 10 run multiple times, Time stays between 0.0005-0.0006, mostly 0.0006.

Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.

2, when the big offset

SELECT * FROM Yanxue8_visit limit 10000, 10 times run, time remains around 0.0187

SELECT * from Yanxue8_visit where vid >= (select Vid-yanxue8_visit order by vid limit 10000,1) limit more than 10 Run, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.

Later should pay attention to correct their own limit statements, optimize the MySQL

Recommended by People Review

The optimization of MySQL is very important. The other most common and most need to optimize is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.

Using the MySQL database, you need to return some or some of the first few lines of data, you will use the limit clause in MySQL. Usually we also use limit to achieve pagination. The specific syntax is as follows:
SQL code
SELECT * FROM table limit [offset,] rows | Rows Offset Offset

SELECT * FROM table limit [offset,] rows | Rows Offset 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. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first row to return the record, and the second parameter specifies the maximum number of rows to be returned. Note: The offset of the initial record line is 0 instead of 1.
Specific examples:
SQL code
SELECT * FROM table limit 5, 10; Retrieve Record Row 6-15

SELECT * FROM table limit 5, 10; Retrieve Record Row 6-15

To retrieve all row rows from an offset to the end of a recordset, you can specify a second argument of-1:
SQL code
SELECT * from table limit 75,-1; Retrieves the record row 75-last.

SELECT * from table limit 75,-1; Retrieves the record row 75-last.

If only one argument is given, it represents the maximum number of record rows returned:
SQL code
Retrieves the first 5 rows of records, in other words, limit n is equivalent to limit 0,n
SELECT * from table limit 5;

MySQL does not allow limit for subqueries.

sql = "SELECT * from users" + PS Tutorial QL + "Limit" + (pcurrentpage_int-1) * getpagesize () + "," + getpagesize (); br> This is my original SQL statement.
When the data is large. The performance is not very good.
I've rewritten
this way

sql = "SELECT * from users" + psql;
if (psql.length () = = 0) {
sql = "Where ID >= (select ID from the users order by ID limit" + (pcurrentpage_int-1) * getpagesize () + ", 1) Limit" + getpagesize ();
} else {
SQL + + ID >= (SELECT id from users + psql + ORDER BY ID limit + (pcurrentpage_int-1) * getpagesize () + ", 1) Limit "+ getpagesize ();
}

I've also encountered limit performance problems, but the performance bottleneck is on the order by. The * is very slow to do order by. My improvement is to do only the ID, identify the desired ID and then select * from table where ID in (idstring), and improve performance. If you are paging, consider using Sql_calc_found_row. I hope it will help you.

Select Sql_calc_found_row ID from table where------limit 0, 50;
Select Found_row ();
Two sentences together to find out how many of the first statements are eligible.