WP_Postmeta terribly slow

There are some queries we didn’t imagine when writing the plugin. So, we’re grateful you brought this to our attention.

If you’re having problems looking up orders by lastname, your performance issue probably doesn’t lie with wp_postmeta but rather wp_users and wp_usermeta, and some other tables.

Please do two things, from within our plugin, so we can understand your problem a bit better.

Visit Tools > Index MySQL > About and upload your MySQL server’s metadata. You’ll get back an ID value. Please let us know what it is.

Then visit Tools > Index MySQL > Monitor Database Operations and start a query monitor to run for a few minutes. With the query monitor running, do some of those badly performing operations. We’ll capture the queries.

Then, when the query monitor’s time ends, view the monitor output (it has its own tab). From within the monitor view click Save as .csv. Then, please email the .csv to oj(at)plumislandmedia(dot)net

We’ll take a look. Thanks again.

(I thought aftermarket exhaust pipes were an American muscle-car thing. I guess they’re a Netherlands thing too. Learn something every day.)

• This reply was modified 1 year, 2 months ago by OllieJones.

Following up:

One slow query is this

SELECT DISTINCT p1.post_id
FROM wp_postmeta p1
WHERE p1.meta_value LIKE 'somebody%'
AND p1.meta_key IN ('_billing_last_name')

If you issue the following command via a MySQL client (like phpmyadmin) the query should be much faster. It is a change to one of the so-called “high-performance keys” installed by the plugin. (Do it at a quiet time on your site; it will lock up your table as it runs.)

ALTER TABLE wp_postmeta 
    DROP KEY meta_key,
    ADD KEY meta_key (meta_key, meta_value(64), post_id);

This adds part of the meta_value column to that key. That should allow MySQL to avoid reading lots of those meta_value columns just to determine they don’t match. (meta_value columns have the LONGTEXT data type, which makes them somewhat expensive to read. Reading lots of them gets very slow, as you have observed.

After you do this, the plugin will suggest you reset the wp_postmeta keys. Ignore that advice: don’t reset the keys.

And use a plugin like Advanced Database Cleaner to remove orphan rows from your wp_postmeta and other tables.

This will be in the next version of the plugin. Thanks for the feedback.

• This reply was modified 1 year, 2 months ago by OllieJones.
• This reply was modified 1 year, 2 months ago by OllieJones.