MySQL True vs False optimization

Question

Can someone explain to me why I'm seeing the following behavior:

mysql> show index from history_historyentry;
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| history_historyentry |          0 | PRIMARY                      |            1 | id          | A         |       48609 |     NULL | NULL   |      | BTREE      |         |  
+----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = False;
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | history_historyentry | ALL  | NULL          | NULL | NULL    | NULL | 48612 | Using where | 
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = True;
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | history_historyentry | ALL  | NULL          | NULL | NULL    | NULL | 48613 | Using where | 
+----+-------------+----------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> create index deleted on history_historyentry (is_deleted) ;
Query OK, 48627 rows affected (0.38 sec)
Records: 48627  Duplicates: 0  Warnings: 0

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = False;
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | history_historyentry | index | deleted       | deleted | 1       | NULL | 36471 | Using where; Using index | 
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(*) FROM `history_historyentry`  WHERE `history_historyentry`.`is_deleted` = True;
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table                | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | history_historyentry | ref  | deleted       | deleted | 1       | const |  166 | Using index | 
+----+-------------+----------------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Why the descrepancy in index usage for True vs False? Specifically, in the false case, the ref column is NULL, and the extra column is Using where; Using index. But in the true case, the ref column is const, and the extra column is Using index.

Answer 1

Presumably because one gives good selectivity and the other does not, i.e. only a small percentage of the rows are deleted.

A cost based optimiser will only use an index if it provides good selectivity (typically 10%) or possibly if it is a covering index (one which satifies a query without a further table or bookmark lookup).