• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
softDelete and database indexing

(08-25-2019, 03:56 AM)MGatner Wrote: 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.

(08-25-2019, 03:56 AM)MGatner Wrote: 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.

Messages In This Thread
softDelete and database indexing - by MGatner - 08-21-2019, 11:23 AM
RE: softDelete and database indexing - by MGatner - 08-24-2019, 06:32 AM
RE: softDelete and database indexing - by MGatner - 08-25-2019, 03:56 AM
RE: softDelete and database indexing - by dave friend - 08-25-2019, 11:25 AM
RE: softDelete and database indexing - by MGatner - 08-25-2019, 04:58 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.