The method of realizing pagination query function in mysql

Editor to share with you the method of mysql to achieve paging query function, I hope you will learn a lot after reading this article, let's discuss it together!

Mysql paging query methods: 1, using specific fields instead of [*]; 2, looking up the index first; 3, using [between … And], id must be continuously increasing; 4. Keep the id where the previous page of the record is located.

The method of mysql paging query:

Brief introduction

Paging queries are usually done in MySQL through limit # {limit}, # {offset}.

When there are more records in the table and the number of pages (# {limit}) is large, the paging query efficiency becomes slower.

When paging the query, the limit + offset records will be queried first, and then the subsequent offset records will be intercepted.

This article takes the actual table big _ table of 140 million as an example to record how to optimize paging queries.

Test description

Test table: big_table, amount of data: 140 million.

Original SQL (4500ms)

-- original paging query, time-consuming: 4500msselect * from big_tablelimit 20000000.10

The reason for the slowness:

1. The query condition is *

2. Limit = 2000000 is too big

Optimization 1 (recommended): replace * (1600ms) with specific fields

-- replace * with explicit fields, time-consuming: 1600msselect id,uid,media_id,media_name,news_id,comment from big_tablelimit 20000001 10

Optimization 2: first look up the index (450ms)

-- method 1: paging the index first, time-consuming: 450msselect * from big_table AS h inner join (select id from big_table limit 20000jue 10) AS ss on h.id = ss.id;-- method 2: first query the index of the starting position, time-consuming: 450msselect * from big_tablewhere id > (select id from big_table limit 2000000Power1) limit 10

Optimization 3: between … And (5ms)

Limitation: id must be incremented continuously.

-- id where the last record is located on the previous page, time consuming: 5msselect * from big_tablewhere id between 4882489 and 4882489 + 10

Optimization 4 (recommended): keep the id where the previous page is located (5ms)

Limit: need to keep the id where the last record on the previous page is located

-- it takes time to keep the id where the last record is located on the previous page: 5msselect * from big_tablewhere id > 4882488limit 10. After reading this article, I believe you have some understanding of mysql's paging query function. You want to know more about it. Welcome to follow the industry information channel. Thank you for reading!