change softdelete column from timestamp to smallint

[jazz7381]

i have problem when record has reached hundred million when using soft delete, the query take a long time.

When i disable soft delete or using withTrashed() the query run normal.
I think change it to smallint will make the query run more faster or we can have 2 column 'is_deleted' and 'deleted_at'

[ousid]

Can you give an example from your code?

[Sophist-UK]

Can you post the long running query as shown in DebugBar and the output from running that same query prefixed with EXPLAIN?

[ousid]

Yes, I think so.

By providing a video explaining my issue to folks who are willing to help me in their free time for free?

[Sophist-UK]

Nah - not you - @jazz7381 who has the long running query on a 100M record table.

[ousid]

Ah! Sorry @Sophist-UK, Since it's a reply, I thought you are talking to me 😄

[Sophist-UK]

An easy mistake to make. Of course I have never done anything like that myself. (Yeah right. Lol)

[Sophist-UK]

Queries that take a long time are normally because the database is scanning the table rather than using an index to find the record.

Have you tried including the deleted_at field in the (unique) index you are using for the table? I would also suggest that this should be put in the index as the first field, as it will then group the non-deleted records at the beginning of the index, and the query will not need to scan the entire index to find all the empty deleted_at fields either.

[Sophist-UK]

See also discussion #41007 and rejected PR #42948.