Is it necessary to use 'Limit 1' for 'SQL SELECT' for performance?

Question

For example, i have a table (users) and its column is name

Case 1:

I know all "name"s values are different (unique) but i not used 'primary key' for this column

Case 2:

I know all "name"s values are different (unique) and i used 'primary key' for this column

---

And if i use both this querys for "Case 1" and "Case 2"

1. SELECT * FROM `users` WHERE `name` = 'Obama'

2. SELECT * FROM `users` WHERE `name` = 'Obama' LIMIT 1

Than what performance difference will be for Case 1 and Case 2?

Answer 1

selecting just 1 result will always be faster than selecting all, whether indexed or not, because LIMIT 1 stops after the first match (think of the case where you have millions of matches. But even with just 2 matches, it is faster to send back 1 result than 2.)

Having an index on the column will typically (but not always) be faster than not having an index, because with an index the mathching rows can be found in O(log n) time, while without an index it takes a linear scan and O(n) time. The exception is when most of the rows match and the simple (and fast) linear scan can find the matches in less time than the overhead of the binary search on the index + main table read.

Edit: if the query is run for an existence test, yes, definitely LIMIT 1. It would be slow and wasteful to compute a million-record result set just to check whether it's empty. (There is also a mysql EXISTS keyword that's very similar, slightly different syntax)