mysql slow query optimization: use limit when available

In many cases, we expect the query result to be at most one record data, so at this time, it is better to use limit 1. When this data is found, mysql will immediately terminate the query, and no more useless queries will be made, thus improving the efficiency.

Let's actually test that in a mysql table with 100000 users, find lily's score (suppose there is only one lily in the system, and we expect that only this data is needed). To show the difference in time, I do not index the name field of the table.

First look at the table structure:

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 of set profiling=1; and execute 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.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 carried out 5 comparative tests on whether to use limit 1. Let's see 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 with limit 1, the efficiency of mysql statement really improves a lot. When the table is larger, the efficiency improvement will be more obvious.  


We have explained the advantages of limit from both theoretical and practical scripts, so the suggestion is: use limit when you can use limit (of course, if the result is multiple, you can't limit 1)








Tags: MySQL

Posted on Fri, 01 May 2020 08:57:19 -0700 by Ashoar