Make WordPress Core

Opened 2 years ago

Closed 5 months ago

#55402 closed enhancement (wontfix)

Check for and retain custom indexes on version upgrades

Reported by: amykamala's profile amykamala Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Database Keywords: reporter-feedback close
Focuses: Cc:

Description

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

Change History (7)

#1 follow-up: @OllieJones
22 months ago

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.

#2 in reply to: ↑ 1 @SergeyBiryukov
22 months ago

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?

#3 @OllieJones
22 months ago

  • Resolution set to invalid
  • Status changed from new to closed

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.

#4 @OllieJones
22 months ago

  • Resolution invalid deleted
  • Status changed from closed to reopened

#5 @OllieJones
21 months ago

  • Resolution set to invalid
  • Status changed from reopened to closed

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/ .

#6 @desrosj
21 months ago

  • Keywords reporter-feedback close added
  • Resolution invalid deleted
  • Status changed from closed to reopened

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.

#7 @hellofromTonya
5 months ago

  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from reopened to closed

It's been 22 months with no additional follow-up. Closing as wontfix. However, please reopen if there is a follow-up for further consideration.

Note: See TracTickets for help on using tickets.