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

Detailed example of installing FastDfs file server using docker compose

docker-compose.yml version: '2' services:...

js to realize the production method of carousel

This article shares the specific code for js to r...

Detailed explanation of six web page image carousel effects implemented with JavaScript

Table of contents 1. When the mouse passes over t...

Meta tags in simple terms

The META tag, commonly referred to as the tag, is...

Implementation of react automatic construction routing

Table of contents sequence 1. Centralized routing...

How to install vim editor in Linux (Ubuntu 18.04)

You can go to the Ubuntu official website to down...

How to use webSocket to update real-time weather in Vue

Table of contents Preface About webSocket operati...

Linux uses NetworkManager to randomly generate your MAC address

Nowadays, whether you are on the sofa at home or ...

Three ways to forward linux ssh port

ssh is one of the two command line tools I use mo...

How to filter out duplicate data when inserting large amounts of data into MySQL

Table of contents 1. Discover the problem 2. Dele...

Vue uses three methods to refresh the page

When we are writing projects, we often encounter ...

Analysis of the situation where js determines and informs the support of CSS attributes (values)

When we want to use a new CSS feature, we always ...

Nginx high concurrency optimization practice

1. Necessity of Tuning​ I have always been reluct...

Specific method of viewing user authorization information in mysql

Specific method: 1. Open Command Prompt 2. Enter ...

Detailed usage of Linux text search command find

The find command is mainly used to find directori...