Hacker News Reader

Comment by BeefWellington | Hacker News Reader

If your column named simply `id` isn't a unique index you've gone very wrong.

The rest of the query plan probably won't need much power.

It really depends on your query engine, this would be considered a "catch all query" in SQL Server, and you're going to have really bad parameter sniffing blowing you up, you do not want to do this usually.

I would expect the query plan for SQL server to essentially return records matching `id` first (which again should be a situation where uniqueness comes into play) and then performing the rest of the execution on the subset that matches, which is hopefully one.

I leave allowances for `id` to be a stand-in for some other identity column that may represent a foreign key to another table. In which case I'd still expect SQL server's query planner to execute as: initial set is those where said column matches the supplied number, then further applies the logic to that subset. In fact I'd love to see where that isn't the case against a transactional DB.

If you include a clustered index/primary key on every query, you might be right, but in practice there's no benefit to include any other params if you have the pk, the catch all query is easy to modify into something that supports batches AND single values (which is where it really gets bad for parameter sniffing) and that's what I usually see in production causing problems.

https://news.ycombinator.com/item?id=42825171

Reply on news.ycombinator.com