"is not null" vs boolean MySQL - Performance

Using WHERE converted_at is not null or WHERE converted = FALSE will probably be the same in matters of query performance.

But if you have this additional bit field, that is used to store whether the converted_at field is Null or not, you'll have to somehow maintain integrity (via triggers?) whenever a new row is added and every time the column is updated. So, this is a de-normalization. And also means more complicated code. Moreover, you'll have at least one more index on the table (which means a bit slower Insert/Update/Delete operations).

Therefore, I don't think it's good to add this bit field.

If you can change the column in question from NULL to NOT NULL (possibly by normalizing the table), you may get some performance gain (at the cost/gain of having more tables).

I had the same question for my own usage. So I decided to put it to the test. So I created all the fields required for the 3 possibilities I imagined:

# option 1
ALTER TABLE mytable ADD deleted_at DATETIME NULL;
ALTER TABLE mytable ADD archived_at DATETIME NULL;

# option 2
ALTER TABLE mytable ADD deleted boolean NOT NULL DEFAULT 0;
ALTER TABLE mytable ADD archived boolean NOT NULL DEFAULT 0;

# option 3
ALTER TABLE mytable ADD invisibility TINYINT(1) UNSIGNED NOT NULL DEFAULT 0
            COMMENT '4 values possible' ;

The last is a bitfield where 1=archived, 2=deleted, 3=deleted + archived

First difference, you have to create indexes for optioon 2 and 3.

CREATE INDEX mytable_deleted_IDX USING BTREE ON mytable (deleted) ;
CREATE INDEX mytable_archived_IDX USING BTREE ON mytable (archived) ;

CREATE INDEX mytable_invisibility_IDX USING BTREE ON mytable (invisibility) ;

Then I tried all of the options using a real life SQL request, on 13k records on the main table, here is how it looks

SELECT *
FROM mytable
LEFT JOIN  table1 ON mytable.id_qcm = table1.id_qcm
LEFT JOIN  table2 ON table2.id_class = mytable.id_class
INNER JOIN  user ON mytable.id_user = user.id_user
where mytable.id_user=1  
and mytable.deleted_at is null  and mytable.archived_at is null
# and deleted=0    
# and invisibility=0  
order BY id_mytable

Used alternatively the above commented filter options.
Used mysql 5.7.21-1 debian9

My conclusion:

The "is null" solution (option 1) is a bit faster, or at least same performance.

The 2 others ("deleted=0" and "invisibility=0") seems in average a bit slower.

But the nullable fields option have decisive advantages: No index to create, easier to update, easier to query. And less storage space used.

(additionnaly inserts & updates virtually should be faster as well, since mysql do not need to update indexes, but you never would be able to notice that).

So you should use the nullable datatime fields option.