MySQL slow query optimization: the advantages of limit from theory and practice

MySQL slow query optimization: the advantages of limit from theory and practice

Many times, we expect the query result to be at most one record of data. In this case, it is best to use limit 1. When this data is found, MySQL will immediately terminate the query and will not perform more useless queries, thereby improving efficiency.

Let's actually test it by looking up Lily's score in a MySQL table with 100,000 entries (assuming there is only one Lily in the system and we only need this piece of data). In order to show the difference in time, I do not create an index on the name field of the table.

Let's take a look at the table structure first:

mysql> show create table tb_province;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_province | CREATE TABLE `tb_province` (
 `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 `score` int(10) unsigned DEFAULT '0',
 `x` int(10) unsigned DEFAULT '0',
 `x1` int(10) unsigned DEFAULT '0',
 `x2` int(10) unsigned DEFAULT '0',
 `x3` int(10) unsigned DEFAULT '0',
 `x4` int(10) unsigned DEFAULT '0',
 `x5` int(10) unsigned DEFAULT '0',
 `x6` int(10) unsigned DEFAULT '0',
 `x7` int(10) unsigned DEFAULT '0',
 `x8` int(10) unsigned DEFAULT '0',
 `x9` int(10) unsigned DEFAULT '0',
 `x10` int(10) unsigned DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=124178 DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We turn on the switch set profiling=1; and execute mysql statements for comparison:

mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)

mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)

mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.04 sec)

mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.02 sec)

mysql> select score from tb_province where name='lily';
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.03 sec)

mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.01 sec)

mysql> select score from tb_province where name='lily' limit 1;
+-------+
| score |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec)

As you can see, we conducted 5 comparative tests on whether to use limit 1. Let's take a look at the results:

mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 5 | 0.02686000 | select score from tb_province where name='lily' |
| 6 | 0.02649050 | select score from tb_province where name='lily' |
| 7 | 0.03413500 | select score from tb_province where name='lily' |
| 8 | 0.02601350 | select score from tb_province where name='lily' |
| 9 | 0.02785775 | select score from tb_province where name='lily' |
| 10 | 0.00042300 | select score from tb_province where name='lily' limit 1 |
| 11 | 0.00043250 | select score from tb_province where name='lily' limit 1 |
| 12 | 0.00044350 | select score from tb_province where name='lily' limit 1 |
| 13 | 0.00053200 | select score from tb_province where name='lily' limit 1 |
| 14 | 0.00043250 | select score from tb_province where name='lily' limit 1 |
+----------+------------+---------------------------------------------------------+
14 rows in set, 1 warning (0.00 sec)

It can be seen that after using limit 1, the efficiency of MySQL statements is indeed greatly improved. When the table is larger, the efficiency improvement will be more obvious.

We have demonstrated the advantages of limit in both theoretical and practical scripts, so the suggestion is: use limit when it is available (of course, if the result is multiple, you definitely cannot use limit 1)

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. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:
  • Mysql slow query optimization method and optimization principle
  • How to optimize MySQL performance through MySQL slow query
  • MySQL slow query optimization and slow query log analysis example tutorial
  • MySQL slow query optimization solution

<<:  Summary of 50+ Utility Functions in JavaScript

>>:  Summary of Linux file directory management commands

Recommend

Analysis of MySQL general query log and slow query log

The logs in MySQL include: error log, binary log,...

Discussion on style customization and browser compatibility issues when using input element [type="file"]

I encountered such a problem when doing the writte...

How to process blob data in MySQL

The specific code is as follows: package epoint.m...

How to check whether the graphics driver has been successfully installed in Ubuntu

Run the command: glxinfo | grep rendering If the ...

Vue global filter concepts, precautions and basic usage methods

Table of contents 1. The concept of filter 1. Cus...

Linux Operation and Maintenance Basic System Disk Management Tutorial

1. Disk partition: 2. fdisk partition If the disk...

Docker automated build Automated Build implementation process diagram

Automated build means using Docker Hub to connect...

Summary of the switching problem and solution of installing multiple JDK versions in win10 64-bit system

Since myeclipse2017 and idea2017 are installed on...

Nginx access log and error log parameter description

illustrate: There are two main types of nginx log...

MySQL 8.0.26 installation and simplified tutorial (the most complete on the Internet)

Table of contents 1. Download MySQL 1.1 Download ...

The most commonly used HTML escape sequence

In HTML, <, >, &, etc. have special mean...

JavaScript implements large file upload processing

Many times when we process file uploads, such as ...

Complete steps for deploying a front-end and back-end separated nginx configuration

Preface It's a cliché. Here I will talk about...

Detailed explanation of the implementation method and usage of CSS3 border-radius rounded corners

In the past, it was quite troublesome to achieve ...

The difference between animation and transition

The difference between CSS3 animation and JS anim...