Mysql fastest paging method, mysql Paging

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.