What is an efficient way to query based on post_meta?

3 Views

The problem I am having is when I allow users to filter posts it overpowers the database and things run extremely slow. I am unsure what the best way to query posts using meta values is. My site has a form that users can submit to filter posts based on checkboxes containing meta values. I currently have the site making an array out of the checkboxes selected for individual post_meta keys like so: 

for ($i=0; $i< count($amenities); $i++)//where $amenities is an array of selected checkbox values

    {        //$arrays is the array that stores the arrays of checkboxes for different meta keys
        $count = count($arrays);
        $arrays[$count] = array(
                    'key' => 'amenities',
                    'value' => $amenities[$i],
                    'compare' => 'LIKE'
                    );
    }

Then I query the database using an array of arrays which contain the values selected for each meta_key. Like this:

Read More
$the_query = new WP_Query(array(
        'post_type' => 'listing',
        'post_status' => 'publish', 
        'category_name' => 'private_rental',
        'orderby' => 'rand',
        'meta_query' => $arrays
    ));

When I do this I get the results I want but it can take over a minute for the results to appear on the screen. Is there a more efficient way of doing this?

Any help would be appreciated!

Post Views: 3

Related posts

Leave a Reply

2 comments

  1. I’d like to see what the actual generated SQL is that WP_Query is using, but for now.

    ( You can use http://wordpress.org/extend/plugins/debug-bar/ to easily view the SQL query )

    Queries ordered by ‘random’ are inefficient. If you don’t need that remove it. If you do need a random order, consider removing it anyway and using PHP’s shuffle to randomize.

    LIKE queries are also fairly inefficient. Use = if you can.

    You might be faster to write a query of your own that pulls the post IDs out of the postmeta table and then use WP_Query with a simple post__in parameter to pull post content. This may or may not help, but is worth the experiment.

  2. You might be able to to take a load off the server by using a meta query using IN:

    $the_query = new WP_Query(array(
        'post_type' => 'listing',
        'post_status' => 'publish', 
        'category_name' => 'private_rental',
        'orderby' => 'rand',
        'meta_query' => array(
                    'key' => 'amenities',
                    'value' => $amenities,
                    'compare' => 'IN'
                    );
    ));

    The query you were trying to do looks like it was complicated, which may have caused excessive server load. IN will basically look for all the posts that have meta data of the key amenities that has a value within the fields the user is looking for. Definitely a more efficient query, imho