Practical Optimization of MySQL Paging Limit

Practical Optimization of MySQL Paging Limit

Preface

When we use query statements, we often need to return the first few or middle rows of data. What should we do at this time? Don't worry, MySQL already provides such a function for us.

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

The LIMIT clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric arguments. The argument must be an integer constant. If two arguments are given, the first argument specifies the offset of the first returned row, and the second argument specifies the maximum number of returned rows. The initial row offset is 0 (not 1): For compatibility with PostgreSQL, MySQL also supports the syntax: LIMIT # OFFSET #.

Therefore, we usually use limit paging when querying data, because this avoids full table query and improves query efficiency. However, when the amount of data in a table increases, paging query will become slower. Let's take a look at the detailed introduction below.

MySQL Paging Limit Optimization

Create a test table card with 20 million data

mysql> select count(*) from card;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (0.00 sec)

-First test the query speed of the first 1000 rows

mysql> select * from card limit 1000,10;
+---------+--------------------------------------+
| card_id | card_number |
+---------+--------------------------------------+
| 1001 | 13fc90a6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1002 | 13fc923e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1003 | 13fc93d5-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1004 | 13fc956a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1005 | 13fc9702-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1006 | 13fc9899-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1007 | 13fc9a31-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1008 | 13fc9bc6-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1009 | 13fc9d5e-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1010 | 13fc9ef5-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.00 sec)

-Test the query after 1 million

mysql> select * from card limit 1000000,10;
+---------+--------------------------------------+
| card_id | card_number |
+---------+--------------------------------------+
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000010 | 2d871039-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.18 sec)

-Test the query after 10 million

mysql> select * from card limit 10000000,10;
+----------+--------------------------------------+
| card_id | card_number |
+----------+--------------------------------------+
| 10000001 | b11ad76c-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000002 | b11aefd5-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000003 | b11af868-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000004 | b11b0031-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000005 | b11b07ad-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000006 | b11b0f0f-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000007 | b11b1669-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000008 | b11b1db2-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000009 | b11b24fa-2e49-11e8-ae62-9c5c8e6e37cf |
| 10000010 | b11b2c37-2e49-11e8-ae62-9c5c8e6e37cf |
+----------+--------------------------------------+
10 rows in set (1.29 sec)

It can be seen that the query efficiency will be lower as time goes by. Because when querying data after 1 million, MySQL will first query 1 million and 10 pieces of data, and then intercept the following ten pieces of data. These will cause performance degradation.

So how to avoid scanning 1 million pieces of data? We can clearly know that the primary key after 1 million is greater than 1 million. So we can rewrite the SQL to use the index and reduce the number of rows scanned

mysql> select * from card where card_id>=1000000 limit 10;
+---------+--------------------------------------+
| card_id | card_number |
+---------+--------------------------------------+
| 1000000 | 2d870088-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000001 | 2d87021a-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000002 | 2d8703ac-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000003 | 2d87053b-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000004 | 2d8706cd-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000005 | 2d87085f-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000006 | 2d8709f1-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000007 | 2d870b83-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000008 | 2d870d18-2e3b-11e8-ae62-9c5c8e6e37cf |
| 1000009 | 2d870eaa-2e3b-11e8-ae62-9c5c8e6e37cf |
+---------+--------------------------------------+
10 rows in set (0.00 sec)

This can greatly improve query efficiency

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:
  • Mysql sorting and paging (order by & limit) and existing pitfalls
  • MySQL uses limit to implement paging example method
  • How to use MySQL limit and solve the problem of large paging
  • A brief discussion on the performance issues of MySQL paging limit
  • Detailed explanation of MySQL Limit performance optimization and paging data performance optimization
  • A brief discussion on the implementation of MySQL's limit paging optimization solution
  • Detailed explanation of MySQL limit usage and performance analysis of paging query statements
  • Usage and points to note of mysql limit paging
  • Optimization method for MYSQL paging limit speed is too slow
  • Reasons and optimization solutions for slow MySQL limit paging with large offsets

<<:  Implementing license plate input function in WeChat applet

>>:  Coexistence of python2 and python3 under centos7 system

Recommend

How to use nodejs to write a data table entity class generation tool for C#

Although Microsoft provides T4 templates, I find ...

Linux file and user management practice

1. Display the files or directories in the /etc d...

How to add fields and comments to a table in sql

1. Add fields: alter table table name ADD field n...

Windows Server 2016 Quick Start Guide to Deploy Remote Desktop Services

Now 2016 server supports multi-site https service...

Summary of learning HTML tags and basic elements

1. Elements and tags in HTML <br />An eleme...

Zen HTML Elements Friends who use zen coding can collect it

html ¶ <html></html> html:xml ¶ <h...

Node connects to MySQL and encapsulates its implementation code for adding, deleting, modifying and checking

Table of contents Node connects to Mysql Install ...

Learn the common methods and techniques in JS arrays and become a master

Table of contents splice() Method join() Method r...

An experienced person will show you how to develop a professional and standardized MySQL startup script

Every qualified Linux operation and maintenance p...

Practical example of nested routes in vue.js Router

Table of contents Preface Setting up with Vue CLI...

Play with the connect function with timeout in Linux

In the previous article, we played with timeouts ...

Common Linux English Error Chinese Translation (Newbies Must Know)

1.command not found command not found 2. No such ...

Summary of some efficient magic operators in JS

JavaScript now releases a new version every year,...

How to understand Vue's simple state management store mode

Table of contents Overview 1. Define store.js 2. ...

Is the tag li a block-level element?

Why can it set the height, but unlike elements lik...