I have a large WP site with about 2k posts and 150k postmeta. When browsing to a large category, the archive loads very slow due to the WP query that is seeking for featured posts, to show them first. I know that the ultimate target is to optimize the postmeta table, but for now I would like to know if there is any possible way to optimize the meta query (one at the bottom) below.

I did some debugging (see the //comments) and found that the 'compare' => '!=' is causing for the query to get really slow. I've also found that '' or '<' is speeding up the query a lot, with the same results, but I am not sure if that is a proper way to go. Please let me know if there is a way to speed up this query. Thanks

function appthemes_addon_on_top_query( $wp_query ){

    $addon_type = $wp_query->get( 'addon_on_top' );

    if( ! $addon_type || ! appthemes_addon_exists( $addon_type ) ) {
        return;
    }

    $addon_info = appthemes_get_addon_info( $addon_type );
    $flag_key   = $addon_info['flag_key']; //custom field for featured posts
    $meta_query = (array) $wp_query->get( 'meta_value', 1 );
    $meta_query = array_filter( $meta_query );
    $meta_query[] = array(
        'relation' => 'OR',
        array(
            'key'     => $flag_key,
            'compare' => 'NOT EXISTS',
        ),
        array(
            'relation' => 'OR',
            //Deleting the array below makes the query quick, but i need it to show featured posts first
            array(
                'key'   => $flag_key,
                'value' => 1,
            ),
            //Deleting the array below makes the query quick, but i need it to properly sorting the posts by date (see below: $wp_query->set( 'orderby')
            array(
                'key'     => $flag_key,
                'value'   => 1,
                'compare' => '!=', //It is about this comparison. '' or '<' makes the query quick, but i am not sure if that is a proper replacement. Also not sure why '!=' makes it this slow.
            ),
        ),
    );

    $wp_query->set( 'meta_query', $meta_query );
    $wp_query->set( 'orderby', array( 'meta_value' => 'DESC', 'date' => 'DESC' ) );
}
up vote 0 down vote accepted

Ok, Ive found that 'compare' => '!=' slows it down, but 'compare' => '<>' does not. I guess both are the same, so I guess this question is solved.

  • unlikely. the only real solution is to reduce the amount of meta, or better, not use meta for searching. what you probably got is some mysql optimization, but optimizers can do only so much when the data structure is bad. – Mark Kaplun May 22 at 12:43
  • I know, but it did enough then. Reduced it by 20 seconds. Anyway, i know that metadata is no good for searching. It is just how the theme works. Thanks for your reply. – RobbTe May 23 at 13:13
  • I guess that if I was you my main fear would have been that it is something local to the server configuration or version. I think for the long term it is better to understand the "why" it had such a dramatic impact. – Mark Kaplun May 23 at 13:37
  • done some googling and found this stackoverflow.com/a/7040370/3984074 (another answer there is very funny, but that one is the useful one) – Mark Kaplun May 23 at 13:40
  • Hi thanks. Ive seen that answer too. I know the why, it has to do with many postmeta rows (118K, after i did some cleaning). However I am not sure why <> is so much quicker over !=. I guess it has to do with <> being MYSQL native. I already did a lot of research on this topic and this is about the best it gets.. I also tried different servers and MYSQL/PHP versions. If anyone has a (better) clue why, please let me know. – RobbTe May 23 at 14:07

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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