MySQL Forums
Forum List  »  General

Foreign key index on multiple columns
Posted by: Ken Gillett
Date: June 14, 2019 11:24AM

Using MySQL 8.0.16 on MacOS.

I have 2 tables, one with a Foreign Key that references the other. The child table also contains another column that I will need to include in an index for fast searches that include its value. I need to not only be able to rapidly retrieve all child records for a specified parent, but also be able to rapidly search for and retrieve child records for that parent that also contain e.g. '123' in this other column.

For any other column I would create an index over both columns which if specified in a right order would enable rapid searching of either one (most important) column or both. But the 'most important' column is the foreign key and although a multiple column index would still work for that and also for my special search, I seem unable to add another column to the index on that foreign key. I'm using WorkBench and that shows the fk index on the one column as 1 which rather indicates I should be able to add column 2, but it won't allow that.

I'm not thinking this is a WB problem, but does MySQL actually allow a fk index on more columns than just the fk itself and if so, why? A multiple column index with the foreign key as column 1 in the index would still work as required, even if there was a column 2 in the index. So why is it not allowed?

Or is this actually a WB problem and in fact MySQL does allow a multiple column index on a foreign key?

If indeed MySQL does not allow this, how best to achieve what I want without having to create another index that does include both columns? This would be wasteful and duplicate the functionality of the existing foreign key index.

Options: ReplyQuote

Written By
Foreign key index on multiple columns
June 14, 2019 11:24AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.