meta_query problem

Question

I have a meta_query that works when its like this:

$args = array(
    'number'     => $users_per_page, 
    'offset'     => $offset ,
    'orderby'    => 'registered', 'order' => 'DESC',
    'meta_query' => array(
        'relation' => 'OR',

        array(
            'relation' => 'AND',

            array(
                'key' => 'referrer',
                'value' => $user_ID,
                'compare' => '='

            ),

            array(
                'key' => 'view_type',
                'value' => 'invoice',
                'compare' => '='
            ),
        ),

        array(
            'relation' => 'AND',

            array(
                'key'     => 'pass_up',
                'value'   => $user_ID,
                'compare' => '='
            ),

            array(
                'key'     => 'view_type',
                'value'   => 'invoice',
                'compare' => '='
            ),
        ), 
    ),
);

As soon as I add an extra query the query times out and breaks the page, and to top it of there is only 5 users in the database.

This code below doesn't work why?

$args = array(
    'number'     => $users_per_page, 
    'offset'     => $offset ,
    'orderby'    => 'registered', 'order' => 'DESC',
    'meta_query' => array(
        'relation' => 'OR',

        array(
            'relation' => 'AND',

            array(
                'key'     => 'referrer',
                'value'   => $user_ID,
                'compare' => '='
            ),

            array(
                'key'     => 'view_type',
                'value'   => 'invoice',
                'compare' => '='
            ),

            array(
                'key'     => 'pass_up',
                'value'   => '$user_ID',
                'compare' => '!='
            ),
        ),

        array(
            'relation' => 'AND',

            array(
                'key' => 'pass_up',
                'value' => $user_ID,
                'compare' => '='

            ),

             array(
                'key' => 'view_type',
                'value' => 'invoice',
                'compare' => '='
            ),
        ), 
    ),
);

Answer 1

Meta queries aren't intended for filtering which posts are returned. You can use them this way, but the database isn't optimised for it. There are few things worse for scaling and performance than meta queries:

• Remote requests to other servers
• Modifying and rescaling images
• Queruing for what you don't want ( e.g. show me all posts but not the posts with this meta key )

Can't I Just Cache this?

Yes and no...

Caching will improve the best possible outcome, but you still have to run the query at least once. Coupled with timing problems your problem won't be as big but you'll still have a problem.

If you do cache the results, cache it in a transient with a long lifetime, and cache the Post IDs of the query and then use them in a new query, if you cache the query object itself you'll still have queries ( WP_Query primes the internal cache saving queries, this won't happen if you cache the object )

All of this is irrelevant though if your queries too slow to complete, it'll never finish in time to be cached

Can I Adjust The Database?

Yes and no..

You can add an Index to the post meta table, but this isn't a true fix because:

• the index will only store the beginning of the meta value, this works well for simple meta values such as a number, but for serialised or longer items it will be just as slow
• the more you store the less efficient the index, storing the entire meta value negates the point of the index
• It's still slow and gives a less than stellar speed bump
• You have to create the index yourself, requiring technical skill many don't have ( some SQL foo )

So How Do I Filter Posts?

Taxonomies!!

If meta queries were fast, we'd use them for categories and tags, but these are implemented instead as taxonomies. You can add your own custom taxonomies to do this, and see a significant speed boost.

Luckily, taxonomy queries have almost identical syntax to meta queries via tax_query. You also get a free admin UI, theme templates ( taxonomy.php etc ), and URL rewriting. You also get a free admin screen listing all your terms, how many posts they have, and the ability to delete/add/edit them with descriptions and term meta.

If you want to, you can disable the post UI and put your own in, as well as plenty of other options. You can also register a taxonomy that has no UI whatsoever. I find these can be useful for mapping things on to eachother, e.g. by using a post ID as the slug/name for a term.

To register a taxonomy, use register_taxonomy.

Here are a handful of useful substitutions:

• get_post_meta -> wp_get_post_terms
• add_post_meta update_post_meta => wp_set_post_terms

There's also:

• get_terms

Bonus Optimisation

Even with taxonomies, this isn't the best query to run. You have too many requirements!

So instead, add a hook to post save, and do the check on save. If it the post meets either condition, set an additional term, say $user_id.'appear-on-invoice-page'. Now your query can look up a single value.

Extra Bonus Optimisation

I noticed your view-type of invoice, this suggests that a single post type is doing the work of several. Consider using register_post_type to create a new post type named invoice. Then you can use 'post_type' => 'invoice' in your query, halving the complexity of your query. This also gives you post archives, an extra menu option in your admin area for invoices, and lots of other benefits ( all of which can be turned on or off when calling register_post_type.

Once all of this is done, your query might look like this:

$args = array(
    'number'   => $users_per_page, 
    'offset'   => $offset ,
    'orderby' => 'registered',
    'order' => 'DESC',
    'post_type' => 'invoice',
    'tax_query' => array(
        'relation' => 'OR',
        array(
            'taxonomy' => 'invoice_referrer', // suggested name for your taxonomy
            'terms' => $user_id
        ),
        array(
            'taxonomy' => 'invoice_pass_up',
            'terms' => $user_id
        )
    )
);

A final note on pagination

I strongly suspect that whatever you do with your page, there is a hidden cost you're paying, the main query. Your use of pagination suggests that you're discarding the main query to create a second query, rather than modifying it. This means that WP has to do all the work of a standard page load before it even attempts to run your query, with all the work wasted.

This is similar to ordering an expensive steak, waiting 30 minutes, then throwing it away and ordering a simple meal. That simple meals going to be very expensive and take a long time to arrive. Wouldn't it be easier to just ask for what you wanted?

Consider using the pre_get_posts filter to modify the main query instead for an additional speed up. This might even let you use the invoice post types archive, giving your a URL, a dedicated template ( archive-invoice.php ), and all you would need to do is set the tax_query options so that users only see those invoices relevant to themselves. The pagination options could be removed, and your post loop would be a standard post loop with no custom WP_Query object.

I'd recommend looking up one of the many great questions/answers already on this site that go into great detail on how to do this