Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.