Mysql fastest paging method, mysql Paging
As we all know, mysql paging is written like this:
select * from 'yourtable' limit start,rows
A table in my database contains million pieces of data. The table name is tweet_data.
select count(*) from tweet_data
Run the first SQL statement and check the first 10 of the 6000000 SQL statements.
How slow is 60 s!
Solution 1,
Someone immediately thought of using indexes to improve efficiency, so we should use the primary key. Therefore, the following SQL
select * from tweet_data where id >=(select id from tweet_data limit 60000000,1)limit 10
View results
Not much improvement, but if the data volume is in the millions, the efficiency can be several times more,! This obviously does not meet our requirements
Solution 2,
The following SQL statement is available:
select * from tweet_data where id_auto_increase between 60000000 and 60000010
See the picture!
Here we only perform operations on the data in a table, and the data volume is about 0.1 billion. But what if we have a larger data volume?
More knowledge is required! Meet me!
The fastest way to get started with MYSQL. Look for some basic books.
How to implement efficient Paging for mysql first look at the basic principles of paging (I use a CSDN million-level database for testing): SELECT * FROM 'csdn 'ORDER BY id DESC LIMIT 100000,2000; time consumption: 0.813ms analysis: description of the above mysql statements: limit 100000,2000 means to scan 102000 rows that meet the condition to throw the first 100000 rows and return 2000 rows. In the case of 120000 rows, the limit 100000,20000 needs to scan 100000 rows of High-concurrency applications, and each query needs to scan more than rows. performance must be greatly reduced pagination using mysql proposes the clue method to use the clue method to provide some clues for page turning. For example, SELECT * FROM 'csdn 'order by id desc. by id, 2000 entries per page are displayed in descending order of current 50th pages. page entry id 102000 small 100000 We only provide the previous page and next page Jump (the N page Jump is provided) SQL statement for processing the previous page: SELECT * FROM 'csdn 'WHERE id <= 102000 order by id desc limit 2000; # Time consumed on the previous page: 0.015ms SQL statement for processing the next page: Time consumed: 0.015ms Sample Page flip: only 20 rows are scanned for each query. This greatly improves the efficiency of sample pages. However, only the previous page and next page links can be provided.