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.

For example:

In the 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 option_id.

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_queries (source), 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?

  • 1
    I don't know the answer to this, but if i were to look into it I'd look at wpdb and at the innards of the dbDelta function which is used to upgrade and create tables
    – Tom J Nowell
    Jan 26, 2022 at 16:58


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.