0

I know that mysql does this, but I don't quite understand why. If you have a child table referencing a parent table, I would understand why an index would be needed on the parent table. When inserting, you have to look up if a row exists in that table.

But why is an index automatically created in the child table? When does mysql use this index?

areke
  • 126
  • 1
  • 8
  • For efficiency in matching the rows, so it can be done just using indexes, without having to read entire rows. – Barmar Mar 04 '20 at 20:41
  • One way to clarify this question, is to add the two table structures you created, and show the indexes that were created as a result. – Ibu Mar 04 '20 at 20:42
  • Have you even tried to look that up on your own? Using Google, an explanation can be found as the very first result, and SO has an answer for that on https://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically – Nico Haase Mar 04 '20 at 20:42
  • @Ibu It's a general question about how MySQL works, not about a specific table structure. – Barmar Mar 04 '20 at 20:43
  • @NicoHaase He knows that it does, his question was WHY. – Barmar Mar 04 '20 at 20:44
  • @Barmar that's what the documentation tells you, for example at https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html: "MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan" – Nico Haase Mar 04 '20 at 20:48
  • yeah the question is why. I read all those questions before and the documentation as well as confirmed it in my own tables. – areke Mar 04 '20 at 20:54
  • my misunderstanding seemed to come from the fact that i thought foreign key checks were only needed when inserting new rows. but if you delete from the parent table, you potentially have to check if you're suddenly violating a foreign key constraint. – areke Mar 04 '20 at 20:56
  • 3
    There is also the idea that foreign keys are often used as join conditions, or looked up based on the key in general... "now that we have the id of the thing, lets get the data associated with that thing". – Uueerdo Mar 04 '20 at 21:02

1 Answers1

2

If you use options like ON DELETE CASCADE and ON DELETE SET NULL, all the related child rows have to be found, and indexes are needed to make this efficient.

Even if you don't use these options, checking foreign keys requires comparing columns in the parent and child tables. Requiring indexes in both tables allows this to be done just using indexes, rather than reading entire rows, which is more efficient.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • so just to confirm my understanding: one index is used on the parent table when inserting rows; the index on the child table is only needed when deleting entries from the parent table? – areke Mar 04 '20 at 20:58
  • They're also used when updating rows, in case a foreign key is changed. And when you join on foreign keys, which is common, they're both used. – Barmar Mar 04 '20 at 21:46
  • with ON DELETE RESTRICT , ON DELETE NO ACTION, ON UPDATE CASCADE, et al. when an attempt is made to delete a row from, or modify a row in the parent table, the database needs to confirm that there are no related rows in the the child table. Without a suitable index available, that would require a full scan, which would be problematic performance-wise. InnoDB actually requires a suitable index to be available when defining a foreign key; InnoDB could have been designed to return an error; but they opted for the design that automatically creates an index. +10 – spencer7593 Mar 04 '20 at 21:46
  • And it does signal an error if the index is missing on the referenced table. They didn't want an operation on one table to automatically modify other tables, but it's reasonable to modify the same table. – Barmar Mar 04 '20 at 21:49