Two indexes with the same field in a MySQL table

Question

For example, we have a 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 an index with two fields, we use it as a single index of the first field.

Is it possible to remove the id_type index without losing performance?

UPDATE: asking this question, you noticed that once the same field in different indices has different power.

Answer 1

MySQL 5.7.9 - Dropping the id_type index does not matter. For both queries, an index of multiple columns ( type_date ) is used.

Explain the query 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, as the latter can be used.

Eliminating disk space and slowing down inserts (a bit).

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

UNIQUE(a), INDEX(a, b) - Now, due to the uniqueness constraint, the former is required. Drop the last one.

On the other hand ... INDEX(a, b) (in my two examples) can be especially useful if it were a "covering" index. That is, if SELECT touched both a , b and other columns. In this case, the query is executed completely in the index structure (BTree) and should not concern the data structure.

Additional information .