Does Laravel's "soft_delete" need index on MySQL?

Question

If i'm using soft delete in laravel 4.2 (database is mysql), every eloquent query has WHERE deleted_at IS NULL. There are no indexes on deleted_at.

• Will it be slow on big tables? (or maybe IS NULL is optimized without needing an index)
• Should I add an index on the deleted_at column?

So, does Laravel's "soft_delete" deleted_at column need an index in MySQL?

Clarification: Laravel stores a timestamp in the deleted_at column to denote when a record has been soft deleted, as opposed to a boolean value.

Answer 1

The column deleted_at is not a good index candidate. I'll try to explain better compared to the comment: indexes are useful only when their cardinality is relatively high. Cardinality is a number that describes index uniqueness in the data-set. That means it's total number of records divided by total unique records.

For example, the cardinality of primary key is 1. Every record contains unique value for primary key. 1 is also, the highest number. You can consider it as a "100%".

But, a column such as deleted_at doesn't have such a value. What Laravel does with deleted_at is check whether it is or isn't null. That means it has two possible values. Columns that contain two values have extremely low cardinality which decreases as number of records goes up.

You can index such a column, but it won't be of any help. What will happen is that it could slow things down and take up space.

TL;DR: no, you don't have to index that column, index will have no beneficial impact on performance.