In some cases, using limit 1 in SQL statements can improve query efficiency if you know that only one query results.
For example, the following user table (primary key ID, mailbox, password):
Copy Code code as follows:
CREATE TABLE T_user (
ID int primary KEY auto_increment,
Email varchar (255),
Password varchar (255)
);
Each user's email is unique, if users use email as a user name landing, you need to check out the corresponding email a record.
SELECT * from T_user WHERE email=?;
The above statement implements a query email corresponding to a user information, but because the email this column is not indexed, will result in a full table scan, inefficient.
SELECT * from T_user WHERE email=? LIMIT 1;
Plus limit 1, as long as you find a corresponding record, it will not continue to scan downward, the efficiency will be greatly improved.
LIMIT 1 applies to SQL statements where the query result is 1 (or 0) that results in a full table scan.
If the email is indexed, you do not need to add limit 1, if it is based on the primary key query a record does not need limit 1, the primary key is also index.
For example:
SELECT * from T_user WHERE id=?;
There is no need to write:
SELECT * from T_user WHERE id=? LIMIT 1;
There is no difference in efficiency.
Attached to the experiment I did:
Stored procedure generates 1 million data:
Copy Code code as follows:
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=0;
While i<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
Copy Code code as follows:
SELECT * from T_user WHERE email= ' 222@xxg.com '; Time-consuming 0.56 S
SELECT * from T_user WHERE email= ' 222@xxg.com ' LIMIT 1; Time-consuming 0.00 S
