Menu
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?
541
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
The answer, in short, is yes. If you limit your result to 1, then even if you are "expecting" one result, the query will be faster because your database wont look through all your records. It will simply stop once it finds a record that matches your query.
The maximum number of clauses in a WHERE clause is 40. LONGVARBINARY and LONGVARCHAR columns can be compared to literals of up to 255 characters in length, but cannot be compared using parameters.
The LIMIT clause can restrict the result set of the query to some maximum number of rows. If this clause specifies a value smaller than the number of qualifying rows, the query returns only a subset of the rows that satisfy the selection criteria.
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.
175
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
