CODEWITHSUNDEEP
mysqlindexing
Hugo
Hugo

Reputation: 171

Mysql index on condition "field IS NULL"

I have a table with a field "deleted_at" which is a DATETIME, and I would like this kind of requests to be executed quickly:

SELECT * FROM table WHERE deleted_at IS NULL

Is it possible to index the "nullness" of the field instead of indexing every single datetime value of my field?

Upvotes: 8

Views: 3200

Answers (1)

StuartLC
StuartLC

Reputation: 107247

You can index nullable columns, however the index will only be used if there is good selectivity on the nulls, i.e. only a small percentage of your data is actually null.

Unfortunately, MySql doesn't support filtered / partial indexes like SqlServer, which allow you to index JUST for rows which are NULL (or NOT NULL).

So TL;DR, if NULLs in deleted_at are the exception, not the rule, then create an index, but this will also index the non-null datetime values as well.

Upvotes: 3

Related Questions