Shulou(Shulou.com)06/01 Report--

The following brings you about the pit content that mysql encountered when querying only one piece of data. I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something from the pit you encounter when you only query one piece of data in mysql.

Scenario: there are user table user, id,email,user_name,mobile and other fields. Now you are required to query a user according to the specified email account.

At first glance, this requirement is simple, so you don't have to think about writing a sql.

Select * from user where email='xxx@yyy.com'

At this point, it is possible to step on the thunder.

If you analyze this minefield, there are the following situations: the 1.email column is not indexed

Under such circumstances, it would be a tragedy. Since there is no index, the entire table is scanned even if the records of the email are not duplicated.

The 2.email column has a normal index

In this case, full table scans can be avoided and indexes can be used, but the where condition will continue to be used for filtering.

The 3.email column has a unique index

This is the most efficient, almost as efficient solution as where id=xxx (provided that id is a primary key index)

When querying only one record, it is best to add a limit 1 restriction after the condition, regardless of whether the where condition has an index or not. In this way, the full table scan can be avoided and the query efficiency can be improved as much as possible. If you develop such a habit of consciousness, you won't step on the pit.

Select * from user where email='xxx@yyy.com' limit 1

Principle: when mysql query, if there is a limit 1 restriction, the query to meet the conditions of the first record will return the result, will not continue the query.

Special circumstances

If the email column does not have an index and happens to be recorded in the last entry of the data table, so that limit 1 actually scans the entire table, it is best to consider indexing the email column.

Do you think you want the pit that mysql encountered when querying only one piece of data above? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.