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

Steps for Docker to build its own local image repository

1. Environment and preparation 1. Ubuntu 14.04 2....

Interpretation of CocosCreator source code: engine startup and main loop

Table of contents Preface preparation Go! text St...

CSS realizes process navigation effect (three methods)

CSS realizes the process navigation effect. The s...

MySQL 5.7.17 winx64 installation and configuration method graphic tutorial

Windows installation mysql-5.7.17-winx64.zip meth...

Sample code for implementing dynamic glowing special effects animation of circles using pure CSS3

This article mainly introduces the sample code of...

CSS makes tips boxes, bubble boxes, and triangles

Sometimes our pages will need some prompt boxes o...

Some tips for using less in Vue projects

Table of contents Preface 1. Style penetration 1....

Summary of Docker configuration container location and tips

Tips for using Docker 1. Clean up all stopped doc...

An article to understand the usage of typeof in js

Table of contents Base Return Type String and Boo...

Why is there this in JS?

Table of contents 1. Demand 2. Solution 3. The fi...

TimePicker in element disables part of the time (disabled to minutes)

The project requirements are: select date and tim...

Summary of common sql statements in Mysql

1. mysql export file: SELECT `pe2e_user_to_compan...

JavaScript to achieve balance digital scrolling effect

Table of contents 1. Implementation Background 2....

MySQL method steps to determine whether it is a subset

Table of contents 1. Problem 2. Solution Option 1...

Writing a shell script in Ubuntu to start automatically at boot (recommended)

The purpose of writing scripts is to avoid having...