Background: I have a plugin with the purpose of changing up the indexes on some of WordPress's tables to make them more efficient for large sites. The plugin changes some primary and other keys for faster data lookups, to exploit MySQL InnoDB's clustered index data structure.
During the WordPress version upgrade (from 5.8.3 to 5.9) the database upgrade process tries to restore WordPress's keys. Most of the SQL statements to do that fail gracefully, and complain in the error log. But some of them add back WordPress standard keys that are redundant to the keys already in place.
wp_comments table the plugin uses
option_name in place of
option_id for the primary key. Most of the query traffic to that table filters on
option_name, so the new primary key lets those queries exploit the clustered index.
The plugin drops the UNIQUE key called
option_name, and adds a UNIQUE key called
The 5.9 update process adds back the UNIQUE key on
option_name, but leaves the high-performance keys unaltered. So now there are two keys on the same column, which is redundant.
Is there a filter (or some other way) for my plugin to examine those SQL statements issued by the core upgrade process and so avoid making the undesired changes to the keys?
There are filters called
dbdelta_insert_queries (source), and
dbdelta_create_queries (source). But it seems my plugin is not in scope while those filters run, so I can't hook them. The version upgrade process hits
upgrade.php directly (source).
What, if anything, am I missing about this? Is there any way to filter those
ALTER TABLE ADD KEY name(column) SQL statements during the database upgrade to avoid the ones creating redundant keys?