MySQL True vs False optimization

Can someone explain to me why I am 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 explanation in using index for True vs False? In particular, if false, the ref column is NULL and the optional column is "Usage Where"; Index usage. But in the true case, the ref column is const and the extra column is using the index.

+1


source to share


1 answer


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



The cost optimizer will only use the index if it provides good selectivity (typically 10%), or perhaps if it is a coverage index (which satisfies the query without further table or bookmark lookups).

+2


source







All Articles