Is it true $wpdb->get_results is faster than WP_Query in most cases?

by

On my homepage, I have a section where I have to display the last five featured posts, where a featured post is simply a post with a custom field is_featured set to 1.

I achieved what I want with two different type of codes:

Using wpdb

<?php    
    $featuredPosts = $wpdb->get_results("
        SELECT ID, post_title FROM $wpdb->posts
        LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
        WHERE $wpdb->postmeta.meta_key = 'is_featured'
        ORDER BY ID DESC LIMIT 5
    ");

    if ($featuredPosts)
    {
        $htmlOutput="";                   

        foreach ($featuredPosts as $featPost)
            $htmlOutput .= '<br /><a href="'.get_permalink($featPost->ID).'">'.$featPost->post_title.'</a>';
    }

    echo $htmlOutput;
?>

According to the “Query Monitor” plugin, this query takes 0.1s and generates this SQL:

SELECT ID, post_title
FROM wp_posts LEFT JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_key = 'is_featured'
ORDER BY ID DESC
LIMIT 5

Using WordPress’ native calls

<?php                                                                       
    $featuredPostsRevised = new WP_Query
    (
        array
        (
            'meta_query' => array
            (
                array
                (
                    'key' => 'is_featured'
                )
            ) 
        ) 
    );  

    while($featuredPostsRevised->have_posts()) : $featuredPostsRevised->the_post();
?>
        <br />
        <a href="https://wordpress.stackexchange.com/questions/151837/<?php the_permalink(); ?>" title="<?php the_title_attribute(); ?>"><?php the_title(); ?></a>
<?php
    endwhile;
?>

According to the “Query Monitor” plugin, this query takes 0.2s and generates a somehow longer SQL:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1
AND wp_posts.post_type="post"
AND (wp_posts.post_status="publish"
OR wp_posts.post_status="private")
AND (wp_postmeta.meta_key = 'is_featured' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10

My questions are:

  1. How can I prepare() my wpdb query? I tried but couldn’t manage because of the second parameter.

  2. After using prepare, am I right in assuming that in terms of security and performance, wpdb is indeed a better solution?

1 Answer
1

How can I prepare() my wpdb query? I tried but couldn’t manage because
of the second parameter.

The prepare() works is explained in Codex, you need to have a query with placeholders and additional arguments to replace that placeholders. If you have no placeholders (so no variable parts in the query) then you can use esc_sql to avoid SQL injections, but that only makes sense when the query is built using user input.
The query in OP, is completely hardcoded, so there is no need to prepare() nor to esc_sql().

After using prepare, am I right in assuming that in terms of security
and performance, wpdb is indeed a better solution?

Short answer: no. WP_Query is just an higher level approach to queries, it builds a query string and then use $wpdb to run that sql query in the database.

If you look at 2 queries in OP their are different, just for example, the query ran using $wpdb doesn’t take into account post status, post date or post password, so if you have deleted, scheduled or private posts, using first query they are shown to users.
Also the query doesn’t take into account results ordering and some other stuff.
Sure you can build a complex query string that takes into account everything and then run it using $wpdb, you have two possibilities:

  1. write a complex hardcoded query string everytime you need a loop: this is really overwhelming and error prone
  2. write some code that handles query in a flexible way: it will be very hard and you should write a lot of code that is already handled by core in WP_Query: that makes no sense.

There is another important factor to consider: WP_Query fires quite a lot filter and action hooks, and thousands of plugins and themes rely on them: by running a manual $wpdb query for loops you will break a lot of things.

So, if you need to query posts use WP_Query: few milliseconds don’t worth hours and hours of code and frustrations. Use $wpdb only when you need to perform queries not handled by core.

It worth adding @TomJNowell comment here:

It should also be kept in mind that WP_Query would allow you to take advantage of a lot of the internal caches WordPress holds, such as retrieving a post once if it’s queried a second time etc. If you have 50 thousand posts to query all at once, you should probably be using a job queue or a CLI script to do your work (unless you have only tens of users and a lot of money to spend on a server)

Leave a Comment