Delete core meta_key INDEX after dbDelta

[mjangda]

We added a new meta_key_value INDEX in #445. Unfortunately, dbDelta only adds INDEXes, but doe not delete them. We should probably have a process that deletes the core INDEX after dbDelta since it's no longer necessary.

[rebeccahum]

@sjinks This might be of interest to you since you're working on CANTINA-852.

[sjinks]

Related: #2893

[sjinks]

It probably makes sense to create a one-time cron job that will run these queries:

ALTER TABLE wp_postmeta LOCK=NONE, ALGORITHM=NOCOPY, ADD KEY vip_meta_key_value (meta_key(191), meta_value(100));
ALTER TABLE wp_postmeta LOCK=NONE, ALGORITHM=NOCOPY, DROP KEY meta_key, ADD KEY meta_key (meta_key(191), meta_value(100));
ALTER TABLE wp_postmeta LOCK=NONE, ALGORITHM=NOCOPY, DROP KEY vip_meta_key_value;

They will normalize the index without locking the tables.

If there are other keys to drop, we can always use

ALTER TABLE table_name LOCK=NONE, ALGORITHM=NOCOPY, DROP KEY index_name;

[github-actions]

This issue has been marked stale because it has been open for 60 days with no activity. If there is no activity within 7 days, it will be closed.

This is an automation to keep issues manageable and actionable and is not a comment on the quality of this issue nor on the work done so far. Closed issues are still valuable to the project and are available to be searched.

[github-actions]

This issue has been marked stale because it has been open for 60 days with no activity. If there is no activity within 7 days, it will be closed.

This is an automation to keep issues manageable and actionable and is not a comment on the quality of this issue nor on the work done so far. Closed issues are still valuable to the project and are available to be searched.