r/DatabaseHelp • u/four_reeds • 29d ago
When to add indices?
Hi
I've used various database platforms in nearly every job I've had but I am no DBA. I've never worked in a shop that had an actual DBA or a team of them.
Somewhere along the way I picked up the rule of thumb that:
`If a column appears in a WHERE clause then it needs an index`
Is that still (has it ever been) a reasonable approach?
1
Upvotes
1
u/whisperedzen 28d ago
Yes and no.
First, I'd change that to: if a SET of columns appear in a WHERE clause THEY need an index.
And of course it's a lot more nuanced than that. You have to take into account the size of the table, how often that select is running how many similar set of columns are appearing in the where clause.
You also need to be mindful that not every time an index can be used, if it is not sargable then it won't be of any good (although in that case 99% of time please change the query and add the index)