SQL index for deleted_at columns

Question

Is it a best practice or beneficial to add an index on the deleted_at column in a SQL database? Lots of my queries use WHERE deleted_at IS NULL, will adding a deleted_at index speed up my queries or slow them down?

Answer 1

@Barmar answered this question best:

"Whether you should index it depends on how well it partitions your table. If most rows are not deleted, indexing it won't help very much. Also, if you have other columns that you index, and you test them along with deleted_at, then you'll need a composite index of the other columns with deleted_at to get the benefit."