Laravel: Are soft deletes really slow?
Well, they CAN be slow if you don’t use the power of indexes
--
One of the key features that Eloquent ORM offers is soft-deletion. This mechanism treat records as “deleted” by just pointing out when they were deleted, instead of destroying the record forever from the database. A Model with Soft Deletes will exclude them automatically every time is queried.
The table for the soft-deletable model looks like this. Note the deleted_at column, that is added by default, but you can change it nonetheless.
|-----------------------------------------------------|
| colors |
|----|---------|----------|-----|---------------------|
| id | user_id | color | ... | deleted_at |
|----|---------|----------|-----|---------------------|
| 1 | 3 | red | ... | 2020-01-01 13:00:00 |
| 2 | 3 | gray | ... | 2020-01-01 13:00:00 |
| 3 | 4 | blue | ... | null |
| 4 | 5 | red | ... | null |
| 5 | 6 | blue | ... | 2019-12-03 00:45:00 |For example, if you call Colors::all() you won’t get the records with id equal to 1, 2 and 5, since these have been marked as deleted, even if they still exists in the database table.
That’s a nice way to have something like a “virtual trash can”. You can restore a deleted record if for some reason you f*ck up, or you need to hold the record in the database to avoid dangling references from other tables.
There is a problem with this approach, though. After doing some benchmarks with thousands of records, performance for this table started to slightly degrade. Suspecting it was the deleted_at column’s fault, I asked something related in StackExchange and I came back with some answers.
To my surprise, it wasn’t.
Word of advice: I will use SQLite for this article for because is braindead easy to work with. Most SQL database engines should have the same behaviour.
Primary key is awesome
When retrieving an Eloquent Model using soft deletion, Eloquent ORM will append WHERE deleted_at IS NULL to the SQL query using a global scope behind the…
