user3772 user3772 - 1 year ago 70
MySQL Question

using sql LIMIT 1 on specific queries

I was wondering if is more memory-efficient to include LIMIT 1 on queries

when we expect 1 result at all times.

So for example I have the following query

select * from clients where client_id = x

I don't have extensive db management skills but I'm assuming that

select * from clients where client_id = x limit 1

would be a bit more memory efficient because the query

wont have to iterate thru each row on the table once it finds

that row.

Am I right or it doesn't matter if I include limit in this specific case?

Answer Source

It could make a big difference if you do not have an index.

Consider the following with no index:

select *
from clients
where client_id = x

The engine will need to scan the entire table to determine that there is only one row. With limit 1 it could stop at the first match.

However, if you have an index, then the index would have the information for equality comparisons. So, the limit would not make a difference in terms of performance. There might be some slight difference, but it should be negligible.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download