I was wondering if there is any point of using LIMIT in a PHP PDO MySQL query, when the result would come back with 1 row anyway (Unless something horrible went wrong). For example, given a table:
CREATE TABLE users (
ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(25)
);
Would there be any performance difference between the following:
$stmt=$db->prepare("SELECT username FROM users WHERE ID=:ID");
or
$stmt=$db->prepare("SELECT username FROM users WHERE ID=:ID LIMIT 1");
EDIT: So I done my own little investigation, and it seems that the difference is in the nanoseconds Lol, and neither method being faster than the other. I just put each statement in a 10000x loop, and recorded a few runs ... On a laptop too, so not scientific or anything Lol ...
Not much for this query, since it is on a primary key.
But if you are doing a query that is less optimized, then yes there would be some small difference between a query with a limit and one without.
Also peace of mind knowing that your query will never return more rows than you expect.
Think about how many queries are being called per page and how many users are loading the page at any one time.
Your may only save a few nanoseconds on a single query but they all add up.
With the clause LIMIT 1 (or more generic LIMIT offset, length) you are guaranteed to return results immediately once the LIMIT reached.
Without such a limit the execution may continue even if no more matches found - so it is less performance effective. The more complex SQL query, the more apparent difference in execution time.
In general, it is always considered a good practice to apply reasonable LIMIT clause.