Check for and retain custom indexes on version upgrades

[amykamala]

Currently Core database schema provides proper indexes for most tables and columns, however some core tables have columns that are not natively indexed, such as the _usermeta meta_value column. For larger enterprise sites that store a significant amount of user data, this becomes a potential performance bottleneck at-scale. As a solution, developers may add their own custom indexes to accommodate more efficient queries. Upon a core database upgrade, these custom indexes get removed and need to be re-added.

To better support developers, WP may want to add a check for custom indexes prior to core DB updates, as well as retain or translate the schema customizations post-upgrade.

https://core.trac.wordpress.org/browser/tags/5.8.1/src/wp-admin/includes/upgrade.php

[OllieJones]

If a plugin could hook the 'dbdelta_queries' filter, it could intercept the data-definition language SQL statements that core update emits. This would be a good way for plugins to control index changes during version updates.

But, unfortunately, plugins aren't active during the database update phase of core's version update process.

Maybe a valid fix to this issue could be a way to make at least some plugins active during version update.

[SergeyBiryukov]

Replying to OllieJones:

If a plugin could hook the 'dbdelta_queries' filter, it could intercept the data-definition language SQL statements that core update emits. This would be a good way for plugins to control index changes during version updates.

But, unfortunately, plugins aren't active during the database update phase of core's version update process.

Indeed. When I briefly ​looked into this previously, it seemed like wp-load.php would load plugins as part of the general bootstrap process. However, I missed that wp-admin/upgrade.php defines WP_INSTALLING as true, and then wp_get_active_and_valid_plugins() returns an empty array​.

So the dbdelta_queries filter is not available for regular plugins. That said, it is available for ​"must-use" plugins (located in wp-content/mu-plugins), which don't check for wp_installing() and are loaded regardless of it​.

Maybe a valid fix to this issue could be a way to make at least some plugins active during version update.

Since "must-use" plugins are always active, would that be helpful here?

[OllieJones]

Yes, putting the code in mu_plugins will work (for me at any rate). Thanks.

It's a good idea to place a minimal amount of code in the path of a version update. Less code: less that can go wrong.

If this can work for @amykamala I don't see any reason to leave this tix open.

[OllieJones]

I got this to work with a filter. It wasn't totally trivial. My writeup is here: ​https://www.plumislandmedia.net/wordpress/filtering-database-changes-during-wordpress-updates/ .

[desrosj]

Because this was closed without follow up feedback from the original reporter, I am reopening this one to allow a bit more time. I'm also adding close so that this one can be closed after a few more months.