MySQL uses limit 1 to improve query efficiency

In the process of web development with a large amount of data, you must carefully think about whether there is a better way to improve the efficiency of program execution. Next we will share a method that is frequently used but easily overlooked in actual work to optimize MySQL query efficiency and use limit 1 rationally.

Currently, there is a table (User table) as follows ):

create table member( id int primary key auto_increment, username varchar(40), password varchar(40) );

The login operations in development are often written as follows:

SELECT * FROM member WHERE `username`='www.phpernote.com';

Or:

SELECT * FROM member WHERE `username`='www.phpernote.com' LIMIT 1;

These two query methods are common, but do you know their actual differences? The results obtained from the author's test with 1 million pieces of data:

The first query statement takes 0.56 s

The second query statement that uses LIMIT 1 takes 0.28 s

It can be seen from the above that in the case of a large amount of data, the appropriate use of LIMIT 1 to optimize the query operation is quite obvious.

Note: If the username in the preceding table fields is set as an index, LIMIT 1 does not have a significant effect on the query speed.

Articles you may be interested in

• Improves the query speed of millions of data records for MySQL
• MySQL limit Query Optimization
• Mysql converts unix timestamp to date format, and MySQL queries the current time
• Summary of the causes and solutions for slow mysql Server Query
• MySQL index Operation Command (create index, re-index, query index, delete index) Summary
• How to query data of mysql today, yesterday, last 7 days, last 30 days, last month, and last month
• 30 methods for optimizing SQL statement query in mysql
• Navicat for mysql remote connection to the mySql database prompt 10061,1045 error Solution