I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed. This method is flawed – here’s the first reason why:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) DEFAULT NULL, `status` enum('archived','active') DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1; mysql> SELECT count(*), status FROM sales GROUP by status; +----------+---------+ | count(*) | status | +----------+---------+ |   65536 | archived | |     154 | active | +----------+---------+ 2 rows in set (0.17 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1 +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra      | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE     | sales | ref | status       | status | 2      | const | 196 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (0.06 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2 +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra      | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE     | sales | ALL | status       | NULL | NULL   | NULL | 65690 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec) |
The cardinality of status index is woeful, but provided that the application [...]
