Foreign key index on multiple columns

[Ken Gillett]

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.