limit 1 worth saying?

[tibberous]

If I am trying to get a single record from a database, by it's unique key, is there any reason to specify 'limit 1'? Now that I think about it, if the key is unique, then saying where key=anything should infer limit 1, an optimization that I would think mysql's designers would not have overlooked.

[rarebit]

key=anything

could be any number of results (depending)

whereas

unique_key=anything

would, yes, be unique

[Mchl]

Adding "LIMIT 1" has additional benefit of telling anyone who reads your code, that the query in question will return no more than 1 row. They don't have to know if key is unique or not for that.

[cooldude832]

Adding "LIMIT 1" has additional benefit of telling anyone who reads your code, that the query in question will return no more than 1 row. They don't have to know if key is unique or not for that.

 

This is true

 

However,

 

Run an EXPLAIN query with the LIMIT attached and witout and examine what MySQL is doing.  Most of the time you can learn right from it if it is a good or bad thing.

 

For the most part I do not LIMIT 1 on querying a single PK.  However as stated it could e a FK and thus an auditer might not see it being a multi row query

[Barand]

if you are expecting multiple results the query would be followed by a while loop.

if you expect only a single row then it will be followed by a single fetch.

 

I think an auditor would spot that.