Is an index required for an InnoDB MySQL foreign key?

Question

MySQL

In Yii2, when automatically generating a migration to create a table with a foreign key, a function is created with the following structure:
- create a table
- create an index on the foreign key field
- create a foreign key
The table is normally created without an index. Some advise not to create indexes when designing tables, only when accumulating several thousand records in a table to speed up queries.
Question 1: why did you build in index creation?
The migration rollback function has the reverse structure:
- delete the foreign key
- delete the index
- delete the table
Question 2: when a table is deleted, the index and the key are not deleted along with it? Or is it different in different DBMS?

Answer 1

Is an index required for an InnoDB MySQL foreign key?

yes required
When creating a table, an index on foreign keys is added automatically.
Here you need to understand what is the difference between an index created by you on any of the fields, and an index on a foreign key. It is not recommended to pre-create indexes, because at the development stage, it is simply not clear which indexes will actually be used in practice, and which ones can be dispensed with (thus saving data recording time). With foreign keys, the story is a little different, they serve to check the integrity of the data, when inserting / editing (I’m generally silent about all sorts of joins there). This means that every time you add a record, you need to find the corresponding record by the foreign key. Search without an index is very slow, so using it by default in this situation is quite justified.
This is the price of independence from the DBMS
Аналогично с предыдущим пунктом, это плата за независимость от СУБД

Answer 2

Вопрос 1: зачем встроили создание индекса?

Потому что индекс можно создавать и не по внешнему ключу.
Потому что индексы иногда необходимо удалять отдельно.