Comment by Guillaume86

Comment by Guillaume86 8 hours ago

5 replies

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.

dotancohen 7 hours ago

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.

  • EvanAnderson 6 hours ago

    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?

    • dotancohen 4 hours ago

      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.

  • buckle8017 6 hours ago

    You are certainly doing something wrong if that's true.

    I'm curious, can you demo this?

    • dotancohen 4 hours ago

      I'm curious as well to see if this still holds up. I'll try this week.