4 ms·
I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tabl
by dotancohen 1y ago
I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.
- Guillaume86 1y agoOnly if your table is missing an unique index on that column, which it should have to enforce your assumption, so yeah LIMIT 1 is a code (or schema in the case) smell.
- dotancohen 1y agoIDs are typically unique primary key. But in my experience, adding LIMIT 1 would on average halve the time taken to retrieve the record. I'll test again, really the last time I tested that was two decades ago.
- buckle8017 1y agoYou are certainly doing something wrong if that's true. I'm curious, can you demo this?
- dotancohen 1y agoI'm curious as well to see if this still holds up. I'll try this week.
- EvanAnderson 1y agoThat seems like your RDBMS wasn't handling something right there or there wasn't a unique index on the column. Do you recall what the database server was?
- dotancohen 1y agoYes, I was using Mysql exclusively at the time. I don't recall which version. I also tested this once years later when doing a Python app with sqlite. Similar result, but admittedly that was not a very big table to begin with. I am meticulous with my database schemas, and periodically review my indexes and covering indexes. I'm no DBA, but I believe that the database is the only real value a codebase has, other than maybe a novel method here and there. So I put care into designing it properly and testing my assumptions.
- viraptor 1y agoThat would be a reportable bug. Of a pretty high priority.
- Guillaume86 1y agoYou should use the DB explain or equivalent command to spit out the query plan, limit 1 shouldn't change anything in your case, if it's not the case you should file an issue, it's pretty much 101 of query optimization.
- giovannibonetti 1y agoI've noticed that LIMIT 1 makes a huge difference when working with LATERAL JOINs in Postgres, even when the WHERE condition has a unique constraint.
- sgarland 1y agoIf you include an ORDER BY, the DB _may_ continue searching. MySQL (and, I assume, MS SQL Server, since it also can cluster the PK) can stop early in some circumstances. But if you just have a LIMIT, then no - any RDBMS should stop as soon as it’s reached your requested limit.
- dotancohen 1y agoRight, that's why I add it.
- fipar 1y agoIn mysql, the db will continue reading even if the limit condition has been met, and then anything beyond the limit will be discarded before returning the result.
- dotancohen 1y agoEven without an ORDER BY clause?
- fipar 1y agoNope, that does work as expected, unless a filesort is required, good point.