1

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.

  • It is a pitty you have editted the question in this way, now there isn't much correlationg between it and the answer.... (just saying) – Mark Kaplun Dec 27 '15 at 21:01
  • @MarkKaplun Should I add SQL back to the question? I was thinking that chokepoints are listed by accepted answer and I don't want to make this question too long and unreadable. – N00b Dec 27 '15 at 21:03
  • na, was just a comment for future questions you might ask. Most readers do not have experience with how the site works and will not know how to look for the previous version. I would say that in this case it is better to add an answer even if you are not going to accept it. – Mark Kaplun Dec 28 '15 at 3:49
3

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.

  • I had no idea.. I start rewriting immediately. Do you also have any comments about the fact that there's 2 exact same queries ( except first has SQL_CALC_FOUND_ROWS, other don't )? Is it normal or there's something very wrong with the theme I started to model for my own needs? – N00b Dec 27 '15 at 16:27
  • ^^ That is actually a good point ^^ See the edit. – s_ha_dum Dec 27 '15 at 16:30
  • There's also a map which goes with the form, there might be slight possibility that it runs same query to show results on map, that would explain 2 queries. Im guessing your recommendation is to get rid of it and somehow use same query for both? This is ruining my day - I've made 10 posts, I don't even want to think what happens if there's thousands of posts.. – N00b Dec 27 '15 at 16:36
  • Honestly, your database server is probably grossly underpowered. This is a hard query but given you have so few posts it shouldn't be slowing to a crawl as you say much less crashing. – s_ha_dum Dec 27 '15 at 16:38
  • Do you know any good articles about sorting posts with PHP or should I ask a new question? PS! Im not using pagination but I need post count in order to use 'offset' because Im loading more posts via ajax when button is pressed. Is it even possible with PHP in my case? I searched for a while but no results. – N00b Dec 27 '15 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.