softDelete and database indexing

That’s interesting about NULL being in-indexable,

NULL being "in-indexable" is not the case for all databases/engines. MySQL/MariaDB using the InnoDB engine apparently does put NULL values in an index and clusters them together at the top. Perhaps that is why WHERE 'column' IS NULL is generally super quick?

You still might run up against "low cardinality" when indexing on 'deleted_at'. Indexing a column that has a small number of values is almost always useless. The optimizer prefers to do a table scan rather than to bounce between the index BTree and the data.

So far I’ve been adding (`deleted_at`, `id`) indexes to all my CI4 tables for models with soft deletes, and often one other like (`deleted_at`, `name`). Does the fact that deleted_at can be null mean these keys are useless? (I’m using MySQL but would like an agnostic solution)

I think those compound indexes might still be useful. However, you might want to rethink the column order. In the case of (`deleted_at`, `id`) if 'id' is the primary index and you're using InnoDB you have actually created a duplicate index. That's because InnoDB "clusters" the PRIMARY KEY with the data. This page of MariaDB documentation explains it better than I. It's a very useful page in other respects.

Another fabulous page packed with useful database info is Rick's RoTs -- Rules of Thumb for MySQL. You might notice a lot of redundancy between that page a lot of the MySQL and MariaDB documentation. That's because Rick's work is the basis for much of the official documentation.

Wish I could offer a more agnostic solution but that's outside my knowledge base.