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 to share