user3772 user3772 - 2 months ago 6
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

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.

Comments