To LIMIT or not to LIMIT?

Question

Let's say a table has an ID column, PRIMARY KEY with AUTOINCREMENT.

If I do a query like:

SELECT * FROM table WHERE ID = ?

is it necessary to add LIMIT 1 ?

Would that increase performance or something?

I'm using SQLite with PHP's PDO.

if you say you have a unique ID then you dont have to have a `LIMIT` — Þaw, Jun 07 '13 at 02:00
Yes, but I was wondering if the engine knows that and stops after the first found record, or it continues to search further.. — thelolcat, Jun 07 '13 at 02:01
run explain SELECT * FROM table WHERE ID = ?, it tells everything. — 蒋艾伦, Jun 07 '13 at 02:03
PDO has nothing to do with your question. It's just a set of commands to send your query and get results provided by database — Your Common Sense, Jun 07 '13 at 04:05

score 5 · Accepted Answer · answered Jun 07 '13 at 02:04

If it is a primary key, then the engine should do the right thing. A primary key index is a unique index, so MySQL knows there is only one matching value.

But even if it doesn't, the impact on performance would be exceedingly minor. The engine would use the primary key index to find the right value, going directly to the right place in the index. It will then load the data page (in order to satisfy the select *).

Then, either it stops right away. Or, it scans and reads the next value in the index, and stops. The additional overhead would be reading the next value in the index. A very minor overhead compared to loading the data page.

If you are interested in such things, you should check out the documentation.

To LIMIT or not to LIMIT?

1 Answers1