Comment by Guillaume86
Comment by Guillaume86 8 hours ago
Only 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.
Comment by Guillaume86 8 hours ago
Only 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.
That 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?
Yes, 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.
You are certainly doing something wrong if that's true.
I'm curious, can you demo this?
I'm curious as well to see if this still holds up. I'll try this week.
IDs 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.