Say I have a table in a sqlite database where the id column is marked UNIQUE (in my actual use case, it's the primary key). Clearly, the query
select foo from some_table where id = 1
will return at most one row. Is there any performance increase by appending limit 1 to the query or is sqlite smart enough to realize that no more rows can possibly match once it finds a single row?
It depends of the query optimizer and of the the optimizer depends of the RDBMS vendor...
In big RDBMS like Oracle or SQL Server, if the id has an UNIQUE or PRIMARY KEY constraint, the LIMIT 1 (wich does not exists in the standard ISO SQL) is remove because it is not necessary. When uniqueness is verified only 0 or 1 row can be returned so limiting to one row is not necessary. This is a classical semantic optimization.
The conclusion is that you can have it, but it is stupid. The performances will be the same... Better removing all unecessary code !
On poor RDBMS, like SQLite or MySQL this kind of optimization is not current, so the system will try to order the single row of the dataset... This is one more operation causing extra execution. SO remove to avoid less performances!
By the way, LIMIT 1 is not standard but
ORDER BY ... OFFSET ... FETCH { PRIOR | NEXT } ...
is !