mysql - SQL查询唯一字段时加上LIMIT 1会不会更快？

Question

比如，用户的username是无重复的，如果查询某个username，可能他会把整个表都搜一遍。
如果我加入了LIMIT 1，他会不会在搜到这个username后立即停止搜索并返回呢？
这种情况下速度是不是就会快一点啊？

Answer 1

where and limit both have the function of avoiding full table scan (mysql). The difference is: where can make full use of 索引, while limit can limit 查询行数

The existence of

limit is mainly to prevent 全表扫描. If a statement itself can be concluded without a full table scan, there will be little difference in performance with or without limit, such as 唯一索引, 主键 [Not tested, NND]

For 偏移量offset larger 查询, it is recommended to use the where statement to avoid 全表扫描; because limit itself does not use the narrowing ability of 索引

For any query, the first thing you should think of is how to use the where statement to narrow the range , and then use limit to limit the number of returned rows

Answer 2

If no index is added, adding limit will make it slower. Just tried it

Answer 3

If a unique index is established for the username field, adding limit is meaningless. Because the implementation of the unique index database is B-Tree search, the only result can be accurately queried

Answer 4

Limit only controls the number of rows returned, not the actual query. You can use explain to check

Answer 5

If you encounter a table with a relatively large amount of data, using limit will kill the database

Answer 6

I have always said that adding LIMIT 1 will speed up the speed

Answer 7

Why not use the unique index of where (such as username). limit just limits the displayed items