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:
|
<<: Summary of 50+ Utility Functions in JavaScript
>>: Summary of Linux file directory management commands
docker-compose.yml version: '2' services:...
This article shares the specific code for js to r...
Table of contents 1. When the mouse passes over t...
The META tag, commonly referred to as the tag, is...
Table of contents sequence 1. Centralized routing...
You can go to the Ubuntu official website to down...
Table of contents Preface About webSocket operati...
Nowadays, whether you are on the sofa at home or ...
ssh is one of the two command line tools I use mo...
Table of contents 1. Discover the problem 2. Dele...
When we are writing projects, we often encounter ...
When we want to use a new CSS feature, we always ...
1. Necessity of Tuning I have always been reluct...
Specific method: 1. Open Command Prompt 2. Enter ...
The find command is mainly used to find directori...