Two indexes with same field in MySQL table

Question

For example we have table:

CREATE TABLE `my_tbl` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `id_type` int(11) NOT NULL,
 `date` date NOT NULL,
 `other_fields` varchar(200) CHARACTER SET latin1 NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_type` (`id_type`),
 KEY `type_date` (`id_type`,`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Ther are two indexes: id_type and id_type, date.

As I know if we have index with two fields we kan use it as single index of first field.

Can I delete index id_type without losing in performance?

UPDATE: Asking this question cause noticed that sometime same field in different indexes has different cardinality.

Answer 1

MySQL 5.7.9 - Dropping id_type index doesnt make a difference. Multi column index(type_date) is applied for both queries.

Explain queries output:

mysql> explain SELECT id_type,date FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+-----



mysql> explain  SELECT id_type FROM my_tbl WHERE id_type='some';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
        +----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_tbl | NULL       | ref  | type_date     | type_date | 4       | const |    1 |   100.00 | Using index |
        +----+-------------+--------+------------+------+---------------+---------


mysql> show indexes from my_tbl;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| my_tbl |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            1 | id_type     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| my_tbl |          1 | type_date |            2 | date        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-------------

Answer 2

INDEX(a), INDEX(a, b) -- Drop the former, since the latter can be used.

Having both wastes disk space and slows down inserts (a little).

INDEX(a, c), INDEX(a, d) -- You might find both of these to be useful.

UNIQUE(a), INDEX(a, b) -- Now, because of the uniqueness constraint, the former is required. Drop the latter.

On the other hand... INDEX(a, b) (in 2 of my examples) could be especially useful if it were a "covering" index. That is, if the SELECT touched both a and b and no other columns. In that case, the query is performed entirely in the index structure (BTree) and does not have to touch the Data structure.

More info.