MySQL forgot about automatically creating an index for a foreign key?

Question

After running the following SQL statements, you will see that, MySQL has automatically created the non-unique index question_tag_tag_id_tag_id on the tag_id column for me after the first ALTER TABLE statement has run.

But after the second ALTER TABLE statement has run, I think MySQL should also automatically create another non-unique index question_tag_question_id_question_id on the question_id column for me.

But as you can see from the SHOW INDEXES statement output, it's not there.

Why does MySQL forget about the second ALTER TABLE statement?

By the way, since I have already created a unique index question_id_tag_id_idx used by both question_id and tag_id columns. Is creating a separate index for each of them redundant?

mysql> DROP DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)

mysql> USE mydatabase;
Database changed
mysql> CREATE TABLE question (id BIGINT AUTO_INCREMENT, html TEXT, PRIMARY KEY(id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE tag (id BIGINT AUTO_INCREMENT, name VARCHAR(10) NOT NULL, UNIQUE INDEX name_idx (name), PRIMARY KEY(id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE question_tag (question_id BIGINT, tag_id BIGINT, UNIQUE INDEX question_id_tag_id_idx (question_id, tag_id), PRIMARY KEY(question_id, tag_id)) ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE question_tag ADD CONSTRAINT question_tag_tag_id_tag_id FOREIGN KEY (tag_id) REFERENCES tag(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE question_tag ADD CONSTRAINT question_tag_question_id_question_id FOREIGN KEY (question_id) REFERENCES question(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM question_tag;
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| question_tag |          0 | PRIMARY                    |            1 | question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | PRIMARY                    |            2 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | question_id_tag_id_idx     |            1 | question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          0 | question_id_tag_id_idx     |            2 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| question_tag |          1 | question_tag_tag_id_tag_id |            1 | tag_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.01 sec)

mysql>

Answer 1

From the documentation:

There must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

question_id is the first column of both the primary key and the question_id_tag_id_idx index. Therefore, no further index is needed for the foreign key. The question_id_tag_id_idx index is actually unnecessary because the primary key has the same columns in the same order. Primary keys are unique clustered indexes.

For the question_tag_tag_id_tag_id foreign key, an index is created because there is no existing index starting with tag_id.

The reason that the question_id_tag_id_idx index (or the primary key) can be used for the foreign key on question_id, but not for the foreign key on tag_id is explained by the section on how MySQL uses indexes in the documentation:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find 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).

The foreign key needs to be able to quickly look up values in the referencing table (question_tag). question_id appears first in question_id_tag_id_idx (is a leftmost prefix), so this index can be used. tag_id appears second, so the index cannot be used.

Answer 2

In a multicolumn index, I believe you get the first column's index for free as if there were an index on only that column.

Update: This is only the case on specific indexes as the other answerer pointed out, that are non-null and become "clustered indexes".