using sql LIMIT 1 on specific queries

Question

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?

If client_id is aprimary key then it'll never be repeated so there's no need to use limit — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Jun 12, 2015 at 12:51

Gordon Linoff · Accepted Answer · 2015-06-12 12:39:03Z

1

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.

edited Jun 12, 2015 at 12:39

answered Jun 12, 2015 at 12:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3772 Over a year ago

I have an index (client_id) is primary key

Gordon Linoff Over a year ago

@user3772 . . . Hence, it shouldn't make a difference.

sathish kumar · Accepted Answer · 2015-06-12 12:41:52Z

0

If client_id is primary index means, no need to use LIMIT 1. Because it will not more effective.

answered Jun 12, 2015 at 12:41

sathish kumar

1161 silver badge9 bronze badges

Collectives™ on Stack Overflow

using sql LIMIT 1 on specific queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related