WordPress has some really awesome functions and classes for retrieving data from the database: WP_Query, WP_User_Query, get_comments(), get_the_terms(), and many, many more. Most of the time these are the functions and classes you should use when querying data from the database–they have some great benefits, such as caching–but there are times when it is actually better to write your own SQL query to retrieve the information and then use the global $wpdb class to execute the query.
The most obvious reason to write your own SQL queries is when your plugin uses custom tables for storing data, such as Restrict Content Pro does for storing payment information. For example, Restrict Content Pro has a member function of a class that is used to retrieve a specific payment from the database, and it looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | /** * Retrieve a specific payment * * @access public * @since 1.5 */ public function get_payment( $payment_id = 0 ) { global $wpdb; $payment = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$this->db_name} WHERE id = %d", absint( $payment_id ) ) ); return $payment; } |
Core WordPress functions and classes are generally excellent when it comes to performance, but there are cases where the “performance boosting aspects” of these actually have an inverse effect. For example, the get_post_meta() function is mostly a wrapper to the get_metadata() function, which takes care of retrieving the requested value from the cache (if it exists) and updating the cache with the requested value if it wasn’t already in the cache. This is excellent for cases where you need to retrieve the same value multiple times, such as subsequent page loads. But what if you just need one single value and you only need it one time, and you want it to be as fast as possible? In this case, it is actually better to write your own custom SQL query so that it bypasses the calls to update_meta_cache().
For example, in Easy Digital Downloads we have a table that shows file downloads by customers, and one of the pieces of data we have to retrieve is the list of files attached to a product. For various reasons, it was faster for us to simply pull the data directly from wp_postmeta than it was to call get_post_meta(). Our $wpdb call looks like this:
1 | $wpdb->get_var( $wpdb->prepare( "SELECT meta_value from $wpdb->postmeta WHERE post_id = %d and meta_key = 'edd_download_files'", $log->post_parent ) ); |
As a general rule of thumb, I’d always recommend sticking with the core WordPress functions for retrieving data, since they are highly optimized and have been tested on a massive scale, but if you know what you’re doing and you fully understand why a direct $wpdb call could be better, go for it.
For anyone that reads this and decides it’s right for them to do as well, if you don’t know *exactly* what you are doing, please read this post by Otto: http://ottopress.com/2013/better-know-a-vulnerability-sql-injection/
+1000 Thank you for linking that, Brian.
Thank you for the link! Danny
Great article Brain
And nobody prevents you from using wp_cache_add/wp_cache_get for your custom queries.
Abstractions are good things to have, as evidenced by the relative ease of using WordPress’ built-in data retrieval capabilities – imagine doing all that more directly with a database handle!
Yet there are times where abstractions get in the way, or just do not accommodate current requirements, as the example showed. In such cases, its a good thing to be able to fall back on a more hands-on approach.
Pippin: How did you measure the speed of get_post_meta vs your custom query?
I’m doing something similar and am just curious how to best make that determination.
Using Query Monitor: http://wordpress.org/plugins/query-monitor/
Thanx for the plugin Pippin
Quick clarification question:
So if we are just pulling 1 piece of metadata and don’t need to cache it, it would be quicker to run a direct query via $wpdb rather than the get_post_meta() function?
Thanks for the post!
As a general rule of thumb I would not recommend avoiding get_post_meta() just because you need one value one time. The function is very efficient and will work exceptionally well in 99.9% of cases.
In the case I mentioned above, it was faster for me to use a direct SQL query because I needed one value one time but for a lot of different posts on the same page. Let’s assume there were 15 posts, so I needed to pull the data 15 times (once for each post). It was faster to use a direct SQL query because it just did one straight query to the database, whereas get_post_meta() would do a query for each one and then also go through the process of updating the post meta cache.
Does that make sense?
Hi, is there a reason why you don’t use $GLOBALS instead of global.
As Toscho stated in http://toscho.de/2012/php-unset-unterschied-global-globals/
if you choose to use global variables it would be better the to use the superglobals. Or doesn’t this count for the wordpress global variable?
Julian
For this, we only needed access to the variables. Since we didn’t need to modify any of the variables in the global scope, using global works just fine 🙂
Thank you a lot!
Just started to dive into developing and really got a lot around in the web searching for best practices , tipps&tricks and coding guidelines and so on. There is a whole universe of plugins and freeware and so on but i don’t want to use them because my target is not to have free stuff but really site and peole to hook in when it comes to reliable and save stuff. For that i really like to pay what i need. I can not code all of that myself that will take me months and years maybe, if i the could do it. So in this short but intensive time i looked for best places to hook in finally i got into your site. And i now, that’s it, don’t need to look for more, all i need is in your space, it’s incredible awesome.
Saw your video chat with franz kaiser and a lot more, so i am a fan already and will stick with you and your products. They sounds like it’s really the best what people can get around there.
May i ask you pre-sales questions in advance to make a collection of your software before i buy just to get the right things together? I stated that already in another area of the forum but may have additional question.
Julian
Yep, you’re more than welcome to ask pre-sale questions! I just ask that you post them on the related product page 🙂
Thank you a lot i would do that like you recommend, but the reason why i ask here is that i am a little bit lost on which specific product page i could do that because i really don’t know where i should place that. It’s more likely a portfolio related question than a product question. I stated that here as thet last entry: https://pippinsplugins.com/jobs-posting-with-easy-content-types-and-gravity-forms/
Maybe you could give me some tips and hints what would be suitable from your perspective to do that. All you recommend me i’ll buy by return.
Julian
I saw your comment on that post and will answer it as soon as I can.
I completely agree that if you can (you’re inside the wp loop) then you should use the built in WordPress functions. We wrote this introductory WPDB tutorial for using the function in case anyone is interested in further reading.