Bug #32634 optimizer does not correctly ignore composite index with low cardinality
Submitted: 22 Nov 2007 20:13
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[22 Nov 2007 20:13] Morgan Tocker
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.