Does Not Play Nice With “Ivory Search” Plugin

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 3 months, 3 weeks ago by OllieJones. Reason: enlarge answer

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.

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.