What you are seeing isn’t “bad” exactly, you’ve just got a terrifically labor intensive query and WP_Query
is not smart enough to optimize it– it doesn’t even try. Briefly, you’ve got a bunch of JOIN
s, you’ve got two LIKE
conditions, which will read every row in the table of that post type, those likes are in a OR
relationship which doubles the work, and you have an bunch of comparison– <=
— operators which will also read every row in the table. All of this, as you’ve seen, gets worse with every condition you add. You are also ORDER
ing by two values, which is also pretty labor intensive. The database engine (not WP_Query
) will make every attempt to optimize the query but it is a hard, hard query to optimize and your database server can’t handle the strain.
You are going to have to try to help things along.
I notice several lines like:
AND ( mt2.meta_key = 'single_rooms'
AND Cast(mt2.meta_value AS signed) >= '3453' )
AND ( mt3.meta_key = 'single_rooms'
AND Cast(mt3.meta_value AS signed) <= '345' )
You don’t post your query arguments but I am guessing you have multiple meta_query
s something like this:
'meta_query' => array(
array(
'key' => 'single_room',
'value' => '3435',
'compare' => '>=',
),
),
Instead of one like this:
'meta_query' => array(
array(
'key' => 'single_room',
'value' => array(3435,345),
'compare' => 'BETWEEN',
),
),
That might cut down on the JOIN
s and speed things up, assuming WP_Query
doesn’t convert that BETWEEN
(which is a valid MySQL query condition).
Don’t order the query. Do that later in PHP. You need a filter, I believe, to completely remove ordering but at least don’t add any more work with your arguments.
You can prevent SQL_CALC_FOUND_ROWS
by adding 'no_found_rows' => true
to your arguments. That can improve performance but might mess up some Loop functions so test it and pay attention.
If that is not enough, query the postmeta
table separately for post IDs and pass those in via the post__in
argument. You will have to write your own SQL. There in not Core function that I am aware of that will query post meta in the way you will need (It is a failure in Core that I complain about periodically)
Beyond that, optimizing is going to mean some very complicated SQL.
One final note: Learn to love EXPLAIN
.