SQL: Is "LIMIT 1" recommended for query where WHERE condition is based on PK ?

Question

I am querying a mySQL database to retrieve the data from 1 particular row. I'm using the table primary key as the WHERE constraint parameter.

E.g.

SELECT name FROM users WHERE userid = 4

The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed benefits?

Answer 1

I would call that a bad practice as when it comes to something like a userid it's generally unique and you won't have more than one. Therefore, having LIMIT 1 seems pretty contradictory and someone who comes to maintain your code later may have to second-guess your design.

Also, I don't think it has any speed benefit at all. You can check out mySQL's Explain for a simple tool to analyze a query.

Note, as mentioned in the comments. LIMIT # does have speed and general benefits in other cases, just not this one.

Answer 2

The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed benefits?

It is not good practice to use LIMIT 1 at the end of the example - it's completely unnecessary, because the userid column is a primary key. A primary key means there is only one row/record in the table with that value, only one row/record will ever be returned.

But the ultimate indicator is the explain plan:

explain SELECT t.name FROM USERS t WHERE t.userid = 4

...returns:

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

...and:

explain SELECT t.name FROM USERS t WHERE t.userid = 4 LIMIT 1

...returns:

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

Conclusion

No difference, no need. It appears to be optimized out in this case (only searching against the primary key).

The LIMIT clause

Using LIMIT without an ORDER BY will return an arbitrary row/record if more than one is returned. For example, using the "John Smith" scenario where 2+ people can have the name "John Smith":

SELECT t.userid
  FROM USERS t
 WHERE t.first_name = 'John'
   AND t.last_name = 'Smith'
 LIMIT 1

...risks returning any of the possible userid values where the first name is "John" and the last name is "Smith". It can't be guaranteed to always return the same value, and the likelihood of getting a different value every time increases with the number of possible records.

Personally I don't care for the use of LIMIT. The syntax isn't supported on Oracle, SQL Server or DB2 - making queries less portable. LIMIT is a tool to be used conservatively, not the first thing you reach for - know when to use aggregate and/or analytic functions.