Menu
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.
IS NULL is optimized without needing an index)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.
548
In MySQL, a primary index is automatically created, and we have already described above how MySQL chooses the primary index. But in the database world, it's actually not necessary to create an index on the primary key column — the primary index can be created on any non primary key column as well.
The USE INDEX ( index_list ) hint tells MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX ( index_list ) tells MySQL to not use some particular index or indexes.
We recommend using compound (multi-column) indexes wherever appropriate, rather than single-column indexes for each column individually.
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.
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.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
