nkiraly/DBSteward — pull request #59 by ajette (2014-05-13 22:17:28+00:00)
Title: Re-use composite index for foreign key index if foreign key column is th...

Here is what is happening now:
On initial table creation there is a single index for DeptID (known as DeptINDEX).
This single index is utilized by mysql for both the foreign key index (which it would implicitly create) as well as the explicitly defined DeptINDEX. There is no reason to have 2 identical indexes so it only allows for definition of one.
When old and new are diffed, a new index is being created for DeptINDEX because mysql still needs an index for the foreign key. Since it was doing double duty and the xml dropped the explicit definition, we need to make sure the foreign key index is still around.
This is why it's currently creating that extra index.

This is an improvement that could be made:
Composite index in mysql can be utilized left to right.
If the first dimension of a composite index is the name of a foreign key then we don't need to recreate the index for the foreign key as it can utilize the composite index.
If it's not the first dimension we need the results to be as they currently are since mysql won't magically recreate the foreign key index, we have to tell it to since we dropped it.