WordPress & MySQL , problem with use many array in meta_query

Question

I use this code for display custom posts :

$args = array(
    'category__in' => $bookcat,
    'meta_query' => array(
        'relation' => 'OR',
        array('key' => 'bookname','compare' => 'LIKE','value' => $bookname),
        array('key' => 'authr','compare' => 'LIKE','value' => $authr),
        array('key' => 'translator','compare' => 'LIKE','value' => $translator),
        array('key' => 'publisher','compare' => 'LIKE','value' => $publisher),
        array('key' => 'pyear','compare' => '=','value' => $pyear,'type' => 'NUMERIC'),
        array('key' => 'ptime','compare' => '=','value' => $ptime,'type' => 'NUMERIC')
        )
    );

There is 6 array in meta_query , when I use that code , mysql server use upper than 80% of cpu! But when I use 4 array or less thats work well.for example :

    'meta_query' => array(
        'relation' => 'OR',
        array('key' => 'bookname','compare' => 'LIKE','value' => $bookname),
        array('key' => 'authr','compare' => 'LIKE','value' => $authr),
        array('key' => 'translator','compare' => 'LIKE','value' => $translator),
        array('key' => 'publisher','compare' => 'LIKE','value' => $publisher)
        )

Is there limit to use number of array in wordpress meta_query?or I have mistake in my code? Thanks for any help.

Answer 1

Looking at your code it looks perfectly fine. Although the more complex you make your query the more resources it is going to take and the longer it is going to take. You mention that your CPU usage rises to 80% which would be normal but how long is the usage holding at 80%? If its for a very short while then I wouldn't worry about it.

What can you do to optimise the query... You could look at placing the data in a separate DB table where you can properly index the table to suit the queries you will be throwing at it. However doing this will require a fair bit of development work to get this done. If you want to go down this route then feel free to ask other questions on here to get your started.

Answer 2

I got the same problem. Issue is that an inner join is generated for each array. In many cases, this is not necessary. I have submitted a patch to core.

See also: WordPress Core - Optimizing meta_query generated SQL?