How to optimize slow queries caused by filtering many meta_query custom fields in WordPress.

If you are working with a custom post type that has many fields you might want to let users filter by those fields. This filtering can be very powerful. With the limitations of a MySQL and more specifically, WordPress’s metadata tables, filtering by custom fields can become slow. This post describes one such scenario, and a few solutions. One ideal solution for WordPress’s metadata tables.

In a recent project I was working on, I built a custom menu system. There were different menus that could be seen, which was a taxonomy. The pages that appear in the menu were a custom post type. Each menu term had 5 different custom fields that were the conditions your account must meet to see that menu. A recent update to the site needed to add 3 new conditions, and that’s where things broke down.

With just three more fields, the query started taking more than 20 seconds to load.

This was a big surprise to me, as nothing drastically changed. A glance at the performance tells me:

• The original menu query took 0.26 seconds (for 5 fields)
• The updated query took 20.91 seconds (for 8 fields)
• I also tested in-between. It took 0.91s for 6 fields and 4.09s seconds for 7 fields.

This wp_termmeta table has 1112 rows, which is trivial in the scope of what MySQL is capable of.

I began digging in to the underlying query with the plugin Query Monitor. I highly recommend Query Monitor if you need to identify slow queries, or for debugging in general. Alternatively, you can take a look at the generated MySQL code with var_dump( $wp_query->request ).

Below is a look at the MySQL code that WordPress has generated. This code is specific to Taxonomy terms and Termmeta, but the same applies to Posts/Postmeta.

After debugging a bit using the EXPLAIN command, I didn’t see any red flags. There is only a handful of rows, all simple queries.

Common solutions (not recommended):

• The most common solution is to add indexes to your table.
  Don’t do this for the name/value columns in wp_postmeta or wp_termmeta. ESPECIALLY for the value column, as indexes will take up an extraordinary amount of disk and memory usage if the site grows to have thousands (or millions) of items.
  
• Another solution is to move searchable data to a new table.
  I actually used this approach the first time (seen here). This works great and the queries are extremely fast. MySQL reports 0.00s query time for the equivalent search from the above query at 20s+. That’s no surprise because there is only about 10 rows. However, this solution takes a lot of work and the pre-defined columns limit your flexibility. Maintenance also becomes more difficult as there is more custom stuff to worry about. You also need to add the hooks when creating (and deleting) posts or terms, to make sure the rows stay in sync.
  
• You might also try to simplify the WHERE clause.
  This was my first thought. It must be the amount of conditions that are bogging down the query. After trial and error, that is just not the case.

The optimal solution:

I can’t take credit for discovering this solution. As a developer most solutions that “I found” are from Stack Overflow anyway. But in this case the solution was pointed out to me by Rosie Leung (see Our Team or check out Rosie’s Portfolio) and it is really magic. Actually, it’s a bandaid.

The problem is that when using JOIN or INNER JOIN, MySQL finds the optimal method to execute the query. It doesn’t always make the right choice, and apparently that’s what is happening during meta_query calls.

The solution is to swap INNER JOIN for STRAIGHT_JOIN in the query.

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.

From the MySQL Manual

The STRAIGHT_JOIN method as far as I can tell, is not commonly recommended outside of very niche scenarios. And this may be one of those.

• The INNER JOIN query took 20.91 seconds (for 8 fields)
• The STRAIGHT_JOIN query took 0.014s (for 8 fields)
• (To be fair, the custom table solution took ~0.000s but has many downsides and takes longer to build)

Implementing this solution:

So far I’ve only implemented this solution for terms, but not for posts. The implementation will be similar for both, though.

For posts (postmeta): You can simply use the “posts_join” filter which gives you access to the joins as a string. Just do a search/replace for INNER JOIN -> STRAIGHT_JOIN. You can adapt the code below which is designed for terms.

For taxonomies (termmeta): You can use the “terms_clauses” filter similar to the above, except this one provides an array of “pieces” which you only want to modify the $pieces["join"].

Here is what the implementation looks like for terms: