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

Summary of the knowledge of embedding instructions that Vue engineers must encapsulate

Table of contents Preface Instruction Basics Hook...

MySQL uses binlog logs to implement data recovery

MySQL binlog is a very important log in MySQL log...

What is this in JavaScript point by point series

Understand this Perhaps you have seen this in oth...

In-depth understanding of Vue's data responsiveness

Table of contents 1. ES syntax getter and setter ...

JS realizes the scrolling effect of announcement online

This article shares the specific code of JS to ac...

The difference between MySQL user management and PostgreSQL user management

1. MySQL User Management [Example 1.1] Log in to ...

Vue implements the sample code of adding, deleting, modifying and checking the tree structure

In fact, many companies have functions similar to...

JavaScript design pattern learning proxy pattern

Table of contents Overview Implementation Protect...

OpenLayers realizes the method of aggregate display of point feature layers

Table of contents 1. Introduction 2. Aggregation ...

Common browser compatibility issues (summary)

Browser compatibility is nothing more than style ...

Detailed explanation of the platform bus of Linux driver

Table of contents 1. Introduction to platform bus...

Introduction to local components in Vue

In Vue, we can define (register) local components...

This article will show you what Vite does to the browser's request

Table of contents Working principle: What does th...

Detailed explanation of the differences between similar tags and attributes in HTML

【1】<i></i> and <em></em> ...

Using cursor loop to read temporary table in Mysql stored procedure

cursor A cursor is a method used to view or proce...