Performance Patch for WP_Meta_Query in WordPress Core

WP_Meta_Query has a problem – it doesn’t perform well when using the OR operator with multiple key/value pairs for comparison. The performance worsens exponentially with each additional key/value pair added to the query. The problem is due to how the SQL query is constructed. Let’s take an example WP_Query containing a meta query:

The above query produces the following SQL statement:

On the install where I ran this test, the raw SQL query execution time was 9.154 seconds on a WordPress install with 4,445 rows in the posts table and 40,453 rows in the postmeta table.

Why is query execution performance so poor?

The root of the problem is the fact that the query is performing three INNER JOIN operations before filtering the results using the WHERE clause. The problem worsens exponentially with every additional meta query parameter, because each parameter results in another INNER JOIN. In this example, the first record has 8 meta values, and is joined to itself three times, which results in 8 * 8 * 8 = 512 rows for one post. Multiply this times the total number of posts, and you can begin to see why it takes so long to run the query.

Additionally, the INNER JOIN operation is tacking the postmeta columns onto the columns for the post, once for each time the INNER JOIN is executed. Although only the ID column is being selected, the key and value columns are needed for comparison, so they need to exist in memory in order to complete the query.

How this can be improved

Basically, WordPress is compiling a massive temporary table in memory, and then paring it down with a series of WHERE clauses. We can improve on that by inverting the logic, and instead selecting from a series of smaller result sets using subqueries:

This query runs in 0.035 seconds, which is about 260x faster for a meta_query with three OR values on this database than the native WordPress meta query SQL.

Increasing the query limit to 100 results in negligible increases in the new query’s execution time – increasing by only 0.001 seconds – whereas the WordPress native meta query increases by 0.795 seconds.

The patch

You can download the patch file and try it yourself.

I have also submitted the patch to Trac #24093.

Join the Conversation

1 Comment

  1. Hi Kevin, I’m writing this comment here as I can’t register to the core development site. While reading the work on the wp-query-patch I saw that you need to test it on sites with large databases – or at least that’s what I understood. I own a wordpress-based site which currently has 46.524 Posts and 431.935 Comments (let me know what other stats you’d need). If you’d like to apply the patch and get any performance metrics before and after let me know and I’ll help as much as I can. Look forward to hearing from you.

Leave a comment

Your email address will not be published. Required fields are marked *