Support » Plugin: Index WP MySQL For Speed » WP_Postmeta terribly slow

  • Resolved iamwebdesignnl

    (@iamwebdesignnl)


    Hi guys,

    I have some issues with a clients site.

    It’s a webshop with 650 products.
    When the client searches for an specific order by lastname, it takes like 20 ~ 30 seconds to get a hit.

    When deleting old orders, it takes forever.

    All other actions and functionalities are quite fast.

    Did all the optimizations by the plugin, with succes.
    But the table is still terribly slow.

    The page I need help with: [log in to see the link]

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author OllieJones

    (@olliejones)

    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.
    Thread Starter iamwebdesignnl

    (@iamwebdesignnl)

    Hi Ollie,

    Thanks for this quick reply!

    I’ve done what you asked. Please let me know if I can do anything else!

    Regards!

    Offtopic: Yeah, this client of mine sends ALOT of custom work to the USA. For Landrover and such.

    Plugin Author OllieJones

    (@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.
    Thread Starter iamwebdesignnl

    (@iamwebdesignnl)

    Pohhh, your a genius.

    Went from 20+ secs for a simple order lookup to 0,19 secs!

    Thanks alot Ollie. I’ll mark it as solved.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘WP_Postmeta terribly slow’ is closed to new replies.