Does the first column of a composite INDEX need another index?

Yada_45066 0
Viewed 350

We have the following table in MySQL.

CREATE TABLE `user_team` (
    `user_id` int(11) NOT NULL,
    `team_id` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`user_id`,`team_id`),
    KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

IS KEY user_id (user_id) required here? I remember reading somewhere saying the first column of a composite index has an index automatically. Can't seem to find the source.

indexing mysql sql
2 Answers

From the MySQL documentation:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Using your example index of (user_id, team_id), a query filtering only on user_id can use this index because the B-tree is first indexed on this field. That it also splits afterwards by team_id doesn't really matter, because you are only looking for all records matching or not matching a certain user_id value or values.

Now if you wanted to also support queries which filter only on the team_id, then you would need to add at least one other index. In this case, your current composite index would not cover this type of query.

user_18632290

KEY (user_id) is totally redundant with the primary key. The primary key also creates an index. A composite index with multiple columns is also an index for each sequence of leading columns. So, an index on (a, b, c) is also an index on (a) and (a, b).

MySQL documentation actually has a good explanation of multi-column indexes. You might want to check it out.

user_11440350
Related