mysql limit optimization methods and detailed instructions

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

The limit clause can be 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. Given two parameters, the first parameter specifies the offset of the first record row, and the second parameter specifies the maximum number of record rows to return. The offset of the initial record line is 0 (not 1): The mysql tutorial also supports the syntax limit # offset # for compatibility with postgresql.

mysql> select * from table limit 5,10; / / retrieve records 6-15

// To retrieve all rows from one offset to the end of the recordset, specify the second argument to -1:

mysql> select * from table limit 95, -1; // Retrieve record line 96-last.

// If only one parameter is given, it means that the maximum number of record rows is returned:

mysql> select * from table limit 5; / / Retrieve the first five records

// In other words, limit n is equivalent to limit 0, n.

Note the difference between limit 10 and limit 9,1:

E.g:

1.

select * from cyclopedia where id> = (
select max (id) from (
select id from cyclopedia order by id limit 90001
) as tmp
) limit 100;

2.

select * from cyclopedia where id> = (
select max (id) from (
select id from cyclopedia order by id limit 90000,1
) as tmp
) limit 100;

The same is to take 90000 after 100 records, the first sentence fast or the second sentence fast?

The first sentence is to take the first 90001 records, whichever is the largest of the id value as a starting identification, and then use it to quickly locate the next 100 records

The second sentence is to choose only take a record after 90000, and then take the id value for the initial identification under the positioning of 100 records

The first sentence execution result .100 rows in set (0.23) sec

The second sentence execution result .100 rows in set (0.19) sec

In fact, the second sentence can be simplified as:

select * from cyclopedia where id> = (
select id from cyclopedia limit 90000,1
) limit 100;

The direct use of the id of the first 90000 records, without having to go through the max operation, in theory, because of the higher efficiency, but in actual use almost can not see the effect, because the return of its own id is a record, max almost no operation Will be able to get the result, but so clear and clearer to write, eliminating the need to draw a snake that foot.

select top 100 * from cyclopedia where id> = (
select top 90001 max (id) from (
select id from cyclopedia order by id
) as tmp
)

However, whether the implementation is a stored procedure or a direct code, the bottleneck is always ms-sql top Always return the first n records, this situation is not deep in the small amount of data, but if hundreds Million, the efficiency will certainly be low. In contrast mysql limit there are more advantages, the implementation of:

select id from cyclopedia limit 90000
select id from cyclopedia limit 90000,1

The results are:

90000 rows in set (0.36) sec
1 row in set (0.06) sec

The ms-sql only select top 90000 id from cyclopedia execution time is 390ms, perform the same operation less than mysql 360ms.

Limit offset (offset) for more records, less records, the offset offset is smaller, the direct use of limit better. The larger the offset, the better the latter.

1, offset relatively small time.

select * from yanxue8_visit limit 10,10

Running multiple times, the time remained between 0.0004-0.0005

select * from yanxue8_visit where vid> = (
select vid from yanxue8_visit order by vid limit 10,1
limit 10

Running multiple times, the time remained between 0.0005-0.0006, mainly 0.0006

Conclusion: Offset offset smaller, the direct use of limit better. This display is the reason for the subquery.

2, offset big time.

select * from yanxue8_visit limit 10000,10

Running multiple times, the time remained at 0.0187 or so

select * from yanxue8_visit where vid> = (

select vid from yanxue8_visit order by vid limit 10000,1

limit 10

Running multiple times, the time remained at around 0.0061, only the former one-third. The larger the offset, the better the latter.

mysql> select * from table limit 95, -1; // Retrieve record line 96-last.