WP_Query times out

Question

I have a form with 20 inputs that runs WP_Query and recieves results via ajax.

For some reason:

• query gets considerably slower when 5+ inputs are filled
• It takes like 10 - 15s with 7 input values
• 15 - 25s with 8 input values and it increases in big chuncks with every next input I've filled
• Finally there's like 15 - 20 values and it times out (server should have 300s timeout)
• Value itself doesn't matter, same results with 1, 4353452 or asadsghthsthhhgfshhds
• Ajax shouldn't have any effect because if there's like 2 inputs filled, there much more data to return, ajax is still fast while file size is bigger. More filled inputs just makes query string longer but it should return less data

---

There's currently 6 tax_queries and 14 meta_queries in total.

What could cause this?

---

Update:

I have 10 example posts, it used to take 20 seconds with 8 filled inputs and it timed out (over 300s) with 20 inputs Now, just after 1 change it takes 0.0012 seconds. Im having a feeling that there was also something else involved because I eliminated foreach and switch in the process and hardcoded everything with if and else if.

Changes I've made so far:

• Was:

---

$meta_query[] = array(
    'key'           => 'some_key',
    'value'         => $some_value,
    'type'          => 'NUMERIC',
    'compare'       => '>='
);
$meta_query[] = array(
    'key'           => 'some_key',
    'value'         => $some_value,
    'type'          => 'NUMERIC',
    'compare'       => '<='
);

• Is:

---

if ( $value_one && $value_two ) {
    $meta_query[] = array(
        'key'           => 'some_key',
        'value'         => array( $value_one, $value_two ),
        'type'          => 'NUMERIC',
        'compare'       => 'BETWEEN'
    );
}
else if ( $value_one && ! $value_two ) {
    $meta_query[] = array(
        'key'           => 'some_key',
        'value'         => $value_one,
        'type'          => 'NUMERIC',
        'compare'       => '>='
    );
}
else if ( ! $value_one && $value_two ) {
    $meta_query[] = array(
        'key'           => 'some_key',
        'value'         => $value_two,
        'type'          => 'NUMERIC',
        'compare'       => '<='
    );
}

• I also eliminated keyword search which uses LIKE operator and it basically searches every post for 2 rounds - titles and content. I figured while user have so many search possibilites, such expensive move is kind of pointless. Im sure that user prefers speed over keyword search.

---

Answer 1

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 JOINs, 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 ORDERing 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_querys 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 JOINs 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.