I was curious, in one doc i read it suggest using limit 1 on all queries if available including indexed and unique columns. Does writing limit 1 actually help queries when using a WHERE uniqueCol = val; ?
Asked
Active
Viewed 524 times
3 Answers
6
No, adding a 'limit 1' doesn't help speed your code up in those cases. Instead, the usual reason why people are doing it, is instead as a 'protection' factor. There are two situations where this can help you:
Sometimes, depending upon your database design, you may THINK that there is only 1 result, but if the column you are selecting upon isn't actually a unique column, for some strange reason, then you might get two results back if the database integrity hasn't been kept. I've seen programmers therefore use limit 1 in such cases, just to ensure that no matter what, they only get 1 result back when they are only expecting 1 result back, and therefore, it doesn't cause the code to fail.
And security. I've also seen people preach the idea of putting a limit 1 in your queries, to make it harder for SQL injection attacks to be very effective. The idea being, that if someone does manage to do SQL injection, that they will only get '1 result' worth. Now the problem with this, is that in practice, this rarely works, since many SQL injection attacks end up ignoring the 'rest of the line' of data.
So, in the end? It certainly doesn't hurt you to put it in there. But there isn't really a solid reason why you should always do it. And in fact, I rarely do it myself, just because the benefits you might convince yourself of, are rather minimal.
Eli
- 869
- 7
- 3
