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 explanation of non-primary key column overflow monitoring in MySQL tables

Today, I fell into the trap again. I have encount...

Detailed explanation of root directory settings in nginx.conf

There are always some problems when configuring n...

Implementation of modifying configuration files in Docker container

1. Enter the container docker run [option] image ...

Detailed explanation of the construction and interface management of Docker private warehouse

1. About Registry The official Docker hub is a go...

You Probably Don’t Need to Use Switch Statements in JavaScript

Table of contents No switch, no complex code bloc...

A summary of the knowledge points of database indexing. Everything you need to know is here.

I believe everyone is familiar with database inde...

Vite introduces the implementation of virtual files

Table of contents background Importing virtual fi...

Detailed explanation of where the image pulled by docker is stored

20200804Addendum: The article may be incorrect. Y...

Detailed explanation of the usage and differences of MySQL views and indexes

MySQL Views Simply put, a MySQL view is a shortcu...

Example of implementing TikTok text shaking effect with CSS

In daily development, front-end students often ar...

Example of adding multi-language function to Vue background management

Table of contents 1. First, configure the main.js...

Differences between ES6 inheritance and ES5 inheritance in js

Table of contents Inheritance ES5 prototype inher...

JavaScript to achieve tab switching effect

This article shares the specific code of JavaScri...

idea combines docker to realize image packaging and one-click deployment

1. Install Docker on the server yum install docke...

How to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog

The two parameters innodb_flush_log_at_trx_commit...