0

I am using a lot of custom post types (CPTs) with custom meta and so far I have been using queries like the below, to get for example all events in CountryX that happened in 2014:

<?php
global $paged;
$curpage = $paged ? $paged : 1;

$args = array(
    'numberposts' => -1,
    'post_type' => 'event',
    'orderby'   => 'event_date',
    'meta_key'  => 'event_date',
    'order' => 'DES',
    'posts_per_page' => 50,
    'paged' => $paged,
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'       => 'event_date',
            'compare'   => '<=',
            'value'     => 20141231,
        ),
        array(
            'key'       => 'event_date',
            'compare'   => '>=',
            'value'     => 20140101,
        ),
        array(
            'key'       => 'event_country',
            'compare'   => 'LIKE',
            'value'     => 'CountryX',
        ),
    ),
);

$the_query = new WP_Query( $args );

I am concerned about the time, RAM and CPU processes that such queries take so I have started reading about custom SQL queries (https://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query).

It is still not clear to me if switching to a custom query will improve speed or there is no improvement whatsoever in my case.

Additionally, since I am just starting with SQL, would appreciate any help in how to start writing queries like the above (with multiple conditional statements).

edit: While we are at it, is using LIKE a good idea to query custom meta fields that have texts and/or arrays?

Thanks in advance

1

In general, custom SQL queries are faster than the WordPress native funtions regardless. The real question is, are these custom SQL queries better, and the simple answer is "NO". Lets look at a few important points to why not to use custom SQL queries:

  • You loose all filters, in the case of WP_Query, you loose pre_get_posts and the posts_* filters

  • Maintaining SQL queries are a shlep. WordPress is an ongoing project and there are constant changes being made which affects the db as well. This might negatively affect your SQL queries and can lead to unnecessary debugging work. With functions and classes like WP_Query and get_post_meta() which uses SQL queries, maintaining functionality with the db is done by the core developers, so they do the hard work. You do not even notice anything on your queries when you update to a version where changes was done to the db

  • Changing SQL queries usually need a complete rewrite, and if you are not confident with SQL syntax, it can lead to other issues. With a class like WP_Query, you can simply add/remove parameters from the array of arguments as necessary

  • You are loosing the complete build-in object cache system, which is there to store data like posts and its relative post data to avoid constant db calls. This cache systems are implemented to improve the performance of a query

meta_query's are always slower than any normal query as it involves joining of tables, and the more nested the array of variables become, the slower the query becomes, this goes for normal SQL queries and for WP_Query calls. The advantage of using WP_Query however is, the first time the query is run, it might be slow as the db is hit hard to get the posts matching a query, but once the query is ran, and everything is saved in the posts object cache, the second time the query is ran, posts are retrieved from the cache, and not the db

You can however make use of transients to store custom queries and flush the transients once a week or month or when needed. This will drastically improve performance as the query will only run when the transient is rebuilded. You can flush transients immediately and rebuild them if a post is updated, deleted, undeleted or published by using the transition_post_status hook. If you need to flush transients when post meta are updated, you can look at actions like updated_post_meta. Here is a nice post about that.

As for your edit, never store custom field values as an array. The sad story here is, if you ever want to order or search for specific values in a custom field, it won't work with serialized data. Store single string values. Also, LIKE is also not desired, because if you search for dot, words like dot, dotcom, and mydot will all be matched which will give undesired results from your query

  • Won't LIKE only match those if the wildcard is used in the query? e.g. %dot%? – phatskat Nov 30 '15 at 15:11
  • No, it doesn't work like that it seems from looking at the SQL build from such a custom query – Pieter Goosen Nov 30 '15 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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