Problems Rekeying wp_postmeta

@josem3784 It looks to me like your key situation on wp_postmeta is different from the one presented at the top of this ticket.

If you use phpmyadmin to give the SQL command SHOW CREATE TABLE wp_postmeta and paste that information here I will be able to figure out what’s wrong and offer you some commands to fix it.

An update to the plugin designed to straighten out these issues is coming soon.

• This reply was modified 3 years, 3 months ago by OllieJones.

@olliejones

Table

wp_postmeta

Create Table

CREATE TABLE wp_postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT 0,
meta_key varchar(191) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
meta_value longtext COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=InnoDB AUTO_INCREMENT=1784730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

Your table’s definition varies from the WP standard in two ways. Neither change was made by this plugin. Your PRIMARY KEY and post_id key both match the WordPress standard.

As you mentioned above, the standard definition has meta_key VARCHAR(250), not 191. This plugin makes no changes to tables’ data definitions, only to keys (a/k/a indexes).

And it has its meta_key index defined without the (191) prefix.

This plugin did not make those changes. Still, you can change your keys back to the WordPress standard definition with these commands.

ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));

I think it’s necessary to give this warning. Avoid using the ALTER TABLE command in MySQL unless you really know what you’re trying to do. And, always always test those commands on a test database you don’t mind wrecking. We, this plugin’s developers, run lots of WordPress instances and MySQL databases on virtual machines that cost nothing to create or destroy. If we make mistakes in development or test, it costs us nothing.

• This reply was modified 3 years, 3 months ago by OllieJones.

What’s up with those prefix keys like ADD KEY meta_key (meta_key(191))?

In the MyISAM-only days of MySQL (the days when WordPress became wildly popular) there was a limit of 767 bytes (not characters) on indexes. Because of the use of utfmb4, that means the index length was limited to 191 characters (up to four bytes/character). A prefix index is still a useful index: queries can use it to get partial matches, then check their matches against the entire column.

But they’re not useful in covering indexes, where the query engine actually gets its result from the index, not the table itself.

The first releases of InnoDB had the same limitation. But, starting with MySQL 5.7, and also in some later point releases of 5.6 the new InnoDB Barracuda engine removed the limitation. This plugin checks carefully for the presence of the Barracuda engine before it rewrites indexes without prefixes. Indexes without prefixes *can* be used in covering indexes, so we add some covering indexes for common queries.

Hi @olliejones thanks for the reply.

Just to be clear, if I make a database backup and then run the commands you gave me:

ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));

Then I will be able to add keys to postmeta from the plugin, Is that correct? Does your plugin will work with meta_key VARCHAR 191?

We changed the meta_key VARCHAR in order to improve performance following the advice of this page:

https://www.spectodesign.com/thoughts/ideas/fixing-slow-woocommerce/

Thanks for that pointer. I don’t believe that’s good advice from SpectoDesign in 2021. I added to the Core ticket about the issue here. https://core.trac.www.ads-software.com/ticket/33885#comment:86

If your MySQL / MariaDB version is 5.7 or beyond, our plugin will replace the prefix indexes meta_key(meta_key(191)) with non-prefix indexes meta_key(meta_key).

I think our plugin will work on your system if you make the index changes I suggested.

@olliejones you need to make sure your backticks are in the right places, it’s not possible to post them here correctly, as the rich editor uses them to style the post.

Hi Ollie & all!
The 191 character limit is being caused by the WP-Optimize (https://www.ads-software.com/plugins/wp-optimize/) “Power Tweaks” setting:

By default, searches on the WordPress “post meta” database table are significantly slower than necessary because the table permits very rarely-used key sizes of longer than 191 characters, which prevents indexing the table. This tweak checks if anything in your database uses such long keys, and if not, creates an index by lowering the limit down to 191 characters.

Changes the table scheme for your postmeta table by reducing the maximum length of the meta_key field down to 191 characters (if nothing already exists longer than that).

Regards,
Brian