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 (1, 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. However, because the email column is not indexed, full table scan is performed and the efficiency is very low.

 

SELECT * FROM t_user WHERE email =? LIMIT 1;

 

With LIMIT 1, as long as a corresponding record is found, the scanning will not continue, and the efficiency will be greatly improved.

 

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:

BEGIN
DECLARE I INT;
Start transaction;
SET I = 0;
WHILE I & lt; 1000000 DO
Insert 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