CODEWITHSUNDEEP
mysqloptimizationindexing
Yogi
Yogi

Reputation: 51

MySQL True vs False optimization

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.

Upvotes: 1

Views: 759

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300569

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).

Upvotes: 2

Related Questions