The Slow Query Optimization of mysql Illustrates the Advantages of limit in Theory and Practice

Most of the time, we expect the query result to be at most one record data, so at this time, it is best to use limit 1. When this data is found, mysql will immediately stop continuing the query without making more useless queries, thus improving the efficiency.

Let's actually test 1, look up the score of lily in an mysql table with 100,000 (assuming there is only one lily in the system, and we only expect to need this data). To show the difference in time, I don't index the name field of the table.

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 set profiling=1; Execute the mysql statement to compare:

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)

It can be seen that we have conducted five 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 adopting limit 1, the efficiency of mysql statement is really improved a lot. When the table is larger, the efficiency improvement will be more obvious.

We have already explained the advantages of limit from both theoretical and practical scripts, so the suggestion is to use limit when limit is available (of course, if the result is multiple, it is definitely not limit 1.)

Summarize