Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Indexing boolean fields

This is probably a really stupid question, but is there going to be much benefit in indexing a boolean field in a database table?

Given a common situation, like "soft-delete" records which are flagged as inactive, and hence most queries include WHERE deleted = 0, would it help to have that field indexed on its own, or should it be combined with the other commonly-searched fields in a different index?

like image 248
nickf Avatar asked Dec 04 '09 05:12

nickf


2 Answers

No.

You index fields that are searched upon and have high selectivity/cardinality. A boolean field's cardinality is obliterated in nearly any table. If anything it will make your writes slower (by an oh so tiny amount).

Maybe you would make it the first field in the clustered index if every query took into account soft deletes?

like image 150
Mark Canlas Avatar answered Sep 25 '22 04:09

Mark Canlas


What is about a deleted_at DATETIME column? There are two benefits.

  1. If you need an unique column like name, you can create and soft-delete a record with the same name multiple times (if you use an unique index on the columns deleted_at AND name)
  2. You can search for recently deleted records.

You query could look like this:

SELECT * FROM xyz WHERE deleted_at IS NULL 
like image 32
jhlllnd Avatar answered Sep 24 '22 04:09

jhlllnd