• Resolved rikq

    (@rikq)


    After installing this plugin and adding the indexes, I noticed immediate slow down on certain admin pages. Specifically, when trying to edit search forms created by the Ivory Search plugin.

    I dug into this using Query Monitor and this is what I found…

    The Ivory Search plugin contains this query on the search form edit page:

    select DISTINCT meta_key from wp_postmeta pt
    LEFT JOIN wp_posts p
    ON (pt.post_id = p.ID) where meta_key NOT LIKE '_%'
    ORDER BY meta_key ASC

    With this “Index WP MySQL For Speed” plugin installed, the Query takes about 30 seconds to run. When I deleted the indexes added by “Index WP MySQL For Speed”, the query executed in less than 2 seconds.

    The same issue was previously reported here.

    Why is this happening?

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

    (@olliejones)

    Please upload your diagnostic metadata according to these directions. https://www.plumislandmedia.net/index-wp-mysql-for-speed/about/ That way I can be sure about my answer.

    It’s quite true that my MariaDB 10.6 server comes up with an inefficient execution plan for this query when the new indexes are in place on postmeta.

    It has to be said, this is a bizarre query, and probably malformed.

    First, both _ and % are wildcard characters in SQL’s LIKE syntax. _ matches any single character, like regex ., and % matches any string of zero or more characters like regex .*. Therefore, meta_key NOT LIKE '_%' actually means “not like any meta_key containing more than one character”. In other words, it ends up matching nothing in most installations (one-character meta_keys being very rare).

    Second, the LEFT JOIN wp_posts p ON (pt.post_id = p.ID) clause accomplishes nothing useful. It looks up the post for each postmeta row and then ignores the result.

    I think the author of that plugin wants to fetch a list of non-hidden post attributes. (Hidden post attributes have meta_key values starting with the _ character.) The author might want to try this query instead.

    select DISTINCT meta_key from wp_postmeta
    where meta_key NOT LIKE '\_%'
    ORDER BY meta_key ASC

    Notice the \_ where the _ wildcard character is escaped with a backslash.

    I guess if the Ivory Search plugin is critical to your operation it would be best to avoid putting the new keys on your postmeta table.

    • This reply was modified 1 year, 4 months ago by OllieJones. Reason: enlarge answer
    Thread Starter rikq

    (@rikq)

    I cannot send you diagnostics information unfortunately. However, I had previously bypassed WordPress completely and ran the query via HeidiSQL. The timings were about the same. Slow with the indexes present. Fast without the indexes.

    I still had the query saved in my HeidiSQL query tab. The backslash IS indeed there. I just copied and pasted without bothering to verify if something was changed in the process. Not sure how the backslash disappeared. Maybe it’s this forum’s new block editor.

    Anyways, hope that helps with troubleshooting this. Thanks.

    Thread Starter rikq

    (@rikq)

    Our wp_postmeta table is by far our largest table in WordPress. We need the indexes mostly for this table.

    Plugin Author OllieJones

    (@olliejones)

    Try adding this key:

    CREATE KEY woo_add_search_to_menu ON wp_postmeta (meta_key, post_id)

    You can do that from HeidiSQL, or you can do it from wp-cli with this command.

    wp db query "CREATE KEY woo_add_search_to_menu ON wp_postmeta (meta_key, post_id)"

    In my MariaDB 10.6 system, this index makes the query faster.

    The key’s name, woo_add_search_to_menu, has a prefix that makes this plugin ignore it, and a name that mentions the slug of the Ivory Search Plugin.

    Please let me know how it turns out.

    Regarding my previous post ^ please disregard the comment about NOT LIKE '_%' . It was based on a misunderstanding.

    • This reply was modified 1 year, 4 months ago by OllieJones.
    Thread Starter rikq

    (@rikq)

    Thank you for your quick replies. I posted the same original question to the support forum of the Ivory Search plugin. Haven’t heard anything back yet.

    The key’s name, woo_add_search_to_menu, has a prefix that makes this plugin ignore it, and a name that mentions the slug of the Ivory Search Plugin.

    Before we run this query to add the new index, could you please explain why/how this helps? We tried looking into the code of the Ivory Search plugin to see if there was anything in it to make it ignore a key named woo_add_search_to_menu. Maybe our search wasn’t thorough enough, but we didn’t find anything. Or maybe this is according to some native behavior of WordPress that we don’t know about.

    While investigating this issue, we temporarily deactivated Index WP MySQL For Speed (after deleting its indexes) and tried a different (paid) plugin that also promises to add indexes among other things. However, that paid plugin is too bloated. We only want the indexes. Nothing more.

    Using only the indexes added by the paid plugin, we did not notice any difference in speed for the query in question. It executed as fast as when no indexes where present.

    Your plugin is dedicated to one thing only. We like that. We also like that it prevents WordPress from deleting the indexes during core updates. Availability of WP CLI is another wonderful feature for big databases like ours. Your team is also a lot more knowledgeable that others. For these reasons, we would like to do our best to keep using Index WP MySQL For Speed.

    More insight about the woo_add_search_to_menu new key would really help.

    Thank you.

    Thread Starter rikq

    (@rikq)

    Update: Just saw your old comment related to this here.

    WooCommerce does put its own key on wp_comments called woo_idx_comment_type. This plugin ignores that key when rekeying that table, leaving it in place. It ignores any keys in any tables with names starting with woo_.

    So, if I understand correctly, we would need to add this key:

    CREATE KEY woo_add_search_to_menu ON wp_postmeta (meta_key, post_id)

    And then rekey our database using Index WP MySQL For Speed.

    I will test this out and let you know.

    Plugin Author OllieJones

    (@olliejones)

    First of all, if you say SHOW CREATE TABLE wp_postmeta (you can mention any table) you’ll see all the columns and indexes.

    The key’s name, woo_add_search_to_menu, has a prefix that makes this plugin ignore it, and a name that mentions the slug of the Ivory Search Plugin.

    I think you figured this out. The woo_ prefix makes our plugin ignore the key, and the rest of the name is simply a clue to remind your future self why this key is present. I suspect none of the queries in Ivory do anything to compel or prohibit the use of any given key.

    If we didn’t ignore the key, our plugin would offer to drop it for you. That’s not what you need.

    You asked,

    Before we run this query to add the new index, could you please explain why/how this helps?

    The query planner did something strange with the multicolumn “high-performance” index when it planned that Ivory query. I don’t understand why. Sometimes the query planner is inscrutable. Plus, you were unable to share your server metadata so I wasn’t able to try to figure out your exact situation.

    Adding the new two-column key on (meta_key, post_id) should, and does, induce the query planner to use it on the query in question. At least on my system it helps with these two queries. The second one is something I guess would serve a site with multiple post_types.

    SELECT DISTINCT meta_key from wp_postmeta pt
    LEFT JOIN wp_posts p
    ON (pt.post_id = p.ID)
    WHERE meta_key NOT LIKE ‘\_%’
    ORDER BY meta_key ASC;

    SELECT DISTINCT meta_key from wp_postmeta pt
    JOIN wp_posts p
    ON pt.post_id = p.ID
    AND p.post_type = ‘product’
    WHERE meta_key NOT LIKE ‘\_%’
    ORDER BY meta_key ASC;

    We tried looking into the code of the Ivory Search plugin to see if there was anything in it to make it ignore a key named woo_add_search_to_menu. Maybe our search wasn’t thorough enough, but we didn’t find anything. Or maybe this is according to some native behavior of WordPress that we don’t know about.

    As mentioned, the key name is only significant to our plugin. You could call it woo_rikq if you wanted.

    Your plugin is dedicated to one thing only. We like that.

    Agreed. I wish more plugins followed the “do one thing well” principle. The kitchen-sink optimization plugins are hard to troubleshoot; they do too much, and they conceal what they do. I suppose that’s because their authors want to make things easy for users. All that works fine until sites get millions of rows, and then some understanding is necessary.

    I hope this helps. Thanks for your patience. I’ll add Ivory to my test matrix.

    Thread Starter rikq

    (@rikq)

    Thank you for the additional information. This is very clear now.

    After further investigation, it appears that the indexes added by Index WP MySQL For Speed only cause slowness when we are browsing certain Ivory Search settings pages. Other admin pages are fast. Frontend pages are fast as well.

    For this reason, we have not bothered adding the woo_add_search_to_menu after all. Ivory Search search forms are “set and forget” for the most part. So, we only need to deal with slowness once in a while.

    One last question:

    If we decide to add this key later woo_add_search_to_menu (for example if we notice slowness for wp_postmeta queries for other areas of our site and identify the cause to be the indexes added by Index WP MySQL For Speed), how would this work with the plugin’s feature to prevent WordPress from deleting the indexes during core updates?

    If a WordPress update attempts to delete indexes, would the new woo_add_search_to_menu be preserved or not. I guess this question is about whether the feature works for all existing database indexes or selectively for only the indexes added by the Index WP MySQL For Speed plugin.

    Thank you!

    Plugin Author OllieJones

    (@olliejones)

    You will be fine on core version updates. That process doesn’t delete any indexes. It only attempts to create the standard indexes if they’re not already present.

    The process of creating the “high-performance” indexes happens to delete a couple of standard indexes (because it rolls them into new primary keys). The update process recreates them (unless our plugin intercepts that operation).

    So, you can create or drop indexes with names starting with woo_ at any time and they’ll stay installed.

    I’ve tested the recent version 6.2 beta upgrade process and it works fine. So you should be good to go.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Does Not Play Nice With “Ivory Search” Plugin’ is closed to new replies.