Description:
The optimizer does not ignore an index if it is part of a composite index with low cardinality. If the index was just on the one column, it would be ignored fine.
Notice that in EXPLAIN number of rows is 98304, but count(*) is 131072.
How to repeat:
mysql> CREATE TABLE t1 (id int, id1 INT, INDEX(id, id1)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1, 2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 64 rows affected (0.01 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 128 rows affected (0.01 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 2048 rows affected (0.05 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 4096 rows affected (0.08 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 8192 rows affected (0.16 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 16384 rows affected (0.34 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 32768 rows affected (0.76 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 65536 rows affected (1.63 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> OPTIMIZE TABLE t1;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (0.19 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE id = 1 AND id1 = 2;
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t1 | index | id | id | 10 | NULL | 98304 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE id = 1;
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t1 | index | id | id | 10 | NULL | 98304 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
Suggested fix:
Ignore the index.