Very slow query with meta_query on large database

2 Views

I have a blog with about 200k posts and each one have 20 custom fields.

I’ve been using WordPress Transients_API to cache queries, in the few cases where it can be used, but it can’t be used in some other queries due to the nature of the blog.

Read More

My blog is http://fixapk.com

There is a problem when accessing applications (single.php).

My code to retrieve the relevant articles using the custom field *’_similar_apps’*, is as follows: 

$args = array(
  'showposts' => 10,
  'post_type' => 'post',
  'meta_query' => array(
    'relation' => 'OR',
     array(
       'key' => '_similar_apps',
       'value' => $mainlink,
       'compare' => 'LIKE'
     ),
   ),
);
$q = new WP_Query( $args );

if ( $q->have_posts() ) : while( $q->have_posts() ): $q->the_post();
  // Do something....

But it is very slow.

You can visit following link to open an article and see its speed

http://fixapk.com/chrome-samsung-support-library/

…it takes about 6-10 second to load.

So, how can I optimize queries with meta_query?

Thank you very much!

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. It is recommended to stick with WP_query for grabbing WordPress posts; but, you can build your own queries directly from the postmeta table, then iterate through the results.

    Depending on the query, this may be faster (Not often; WordPress works hard to make their database queries as fast as they can be).

    global $wpdb;
$table = $wpdb->prefix . 'the_table_you_want';
$sql = $wpdb->prepare( 'SELECT id FROM ' . $table . ' WHERE some_field = %s AND another_field = %d;', 'some_value', 42 );
$results = $wpdb->get_results( $sql );

foreach( $results as $result ):

    //do stuff with $result ( it is an object, not an array )       

endforeach;

    ALWAYS use prepare when constructing your own queries.