MySQL query optimization: Limit 1 avoids full table Scan

In some cases, if only one query result is known, using limit 1 in SQL statements will increase the query efficiency.

 

For example, the following User table (primary key ID, email address, and password ):

 

create table t_user(id int primary key auto_increment,email varchar(255),password varchar(255));

 

 

Each user's email is unique. If you use email as the user name to log in, you need to query a record corresponding to the email.

Select * From t_user where email = ?;

 

The preceding statement queries the user information corresponding to an email.No Index added, Will causeFull table Scan, The efficiency will be very low.

 

Select * From t_user where email =? Limit 1;

 

Add limit 1,As long as a corresponding record is found, the scan will not continue.And greatly improve the efficiency.

 

Limit 1 is applicable to SQL statements with a query result of 1 (or 0) that may cause full table scan.

 

If email is an index, you do not need to add limit 1. If you want to query a record based on the primary key, you do not need limit 1. The primary key is also an index.

For example:

Select * From t_user where id = ?;

You do not need to write it as follows:

Select * From t_user where id =? Limit 1;

There is no difference in efficiency between the two.

 

Attached is my experiment:

 

The stored procedure generates 1 million data records:

 

BEGINDECLARE i INT;START TRANSACTION;SET i=0;WHILE i<1000000 DOINSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@xxg.com'),i+1);SET i=i+1;END WHILE;COMMIT;END

 

 

Query statement

Select * From t_user where email = '2017 @ xxg.com '; time consumed: 222 s

Select * From t_user where email = '2017 @ xxg.com 'limit 1; time consumed: 222 s

Author: Cross brother reprint please indicate the source: http://blog.csdn.net/xiao__gui/article/details/8726272