Pagination usage and performance optimization of MySQL limit

MySQL Tutorial limit performance issues

There is a tens of millions of-record table on MySQL 5.0.x, now read out a few 1 billion records

Common methods, followed by loops:
SELECT * FROM mytable where index_col = xxx limit offset, limit;

Experience: If there is no blob/text field, the single record is small, you can set the limit to a large point, will speed up
Problem: The first tens of thousands of reads quickly, but the speed is linearly decreasing while the MySQL server CPU 99%
Speed is not acceptable.

Call Explain select * FROM mytable where index_col = xxx limit offset, limit;
Show type = All

In the MySQL optimization document written to "all" explanation
A full table scan is doing for each combination of rows from the previous tables. This is normally not good if the table is the marked const, and usually very bad in all other cases. Normally, can avoid all by adding indexes this allow row retrieval from the table based on constant values or column V Alues from earlier tables.

It seems that for all, MySQL uses the more stupid method, that is to use range mode?

Because the ID is incremented, it's also good to modify the SQL

SELECT * FROM mytable where ID > offset and ID < offset + limit and index_col = xxx

Explain display type = range, the result is very fast, the result is dozens of times times faster.

I'm interested in using a lot of limit keywords in MySQL queries, because in my impression, the limit keyword seems to be more used by programmers using MySQL database tutorials to do query paging (which is also a good query optimization), so here's an example Assuming we need a paging query, Oracle typically uses the following SQL sentences:

SELECT * FROM

(select a1.*, RowNum rownum_

from TestTable A1

where rownum > 20)

where Rownum_ <= 1000

This statement will be able to query to the TestTable table 20 to 1000 records, but also need nested query, the efficiency is not too high, to see the implementation of MySQL:

SELECT * FROM testtable A1 limit 20,980;

This will return a record of 21 to (20 + 980 =) 1000 in the TestTable table.

Implementing the syntax is really simple, but if you want to say the efficiency of the two SQL statements here, it's hard to compare, because there are many different ways to interpret the limit option in MySQL, and the speed difference in different modes is great, so we can't say whose efficiency is high from the simplicity of this statement.

But for programmers, simple enough, because the maintenance cost is low, hehe.

Let's talk about this limit grammar:

Select ... other parameters for--select statements

[Limit {[offset,] row_count | row_count offset Offset}]

Offset here is the offsets (the starting address of this offset is 0, not 1, which is easily mistaken) as the name suggests is the location to leave the starting point, and Row-count is also very simple, is the number of records to return the limit.

eg. SELECT * from TestTable a limit 10,20 where ....

This allows the result to return 20 records that match the Where condition after 10 rows (including 10 lines themselves).

Then, if there is no constraint, a record of 10 to 29 rows is returned.

So what does this have to do with avoiding full table scans? Here are some instructions for the Limit parameter optimization scan in the MySQL manual:

In some cases, MySQL will process the query differently when you use the Limit option instead of having it.

L If you use limit to select only a subset of the rows, when MySQL usually does a full table scan, but in some cases the index (related to IPART) is used.

L If you use limit n with order by, when MySQL finds the first qualifying record, it ends the sort instead of sorting the entire table.

l when limit N and distinct are in use at the same time, MySQL stops the query after it finds a record.

In some cases, group by can be resolved by sequentially reading the key (or sorting on the key), and then calculating the digest until the key value changes. In this case, limit n will not compute any unnecessary group.

L when MySQL completes sending the nth row to the client, it discards the remaining query.

L and the Limit 0 option always returns an empty record quickly. This is useful for checking the query and getting the column type of the result column.

L The size of a temporary table uses limit # to calculate how much space is needed to resolve the query.

Millions of data Blur lookup big improvement!!!!!! (0.03 sec)

Select Id,name from user where name like '%83% ' or key like '%83% ' limit 0, 25;

Paging

The usage of limit in MySQL [Data paging common]

When we use the query statement, often to return the first few or the middle of a few lines of data, this time how to do? Don't worry, MySQL has provided us with such a feature.
SELECT * FROM table limit [offset,] rows | Rows Offset Offset

The limit clause can be used to force a 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. The offset of the initial record line is 0 (instead of 1): In order to be compatible with PostgreSQL, MySQL also supports syntax: Limit # offset #.

Mysql> 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:
Mysql> select * from table limit 95,-1; Retrieves the record row 96-last.

If only one argument is given, it represents the maximum number of record rows returned:
Mysql> select * from table limit 5; Retrieve the first 5 rows of records

In other words, limit n is equivalent to limit 0,n.
1. Select * FROM tablename < conditional statement > limit 100,15

Start with 100 records after 15 (the actual fetch is 第101-115条 data)

2. Select * FROM tablename < conditional statement > limit 100,-1

Start after 100th-record of the last article

3. SELECT * FROM tablename < conditional statement > limit 15

Equivalent to limit 0,15. Query results take the first 15 data