I need to get a bunch of posts with their metadata. Of course you can't get metadata with a standard posts query, so you generally have to do a get_post_custom() for each post.

I'm trying with one custom query, like this:

$results = $wpdb->get_results("
    SELECT  p.ID,
        pm1.meta_value AS first_field,
        pm2.meta_value AS second_field,
        pm3.meta_value AS third_field
    FROM    $wpdb->posts p LEFT JOIN $wpdb->postmeta pm1 ON (
            pm1.post_id = p.ID  AND
            pm1.meta_key    = 'first_field_key'
        ) LEFT JOIN $wpdb->postmeta pm2 ON (
            pm2.post_id = p.ID  AND
            pm2.meta_key    = 'second_field_key'
        ) LEFT JOIN $wpdb->postmeta pm3 ON (
            pm3.post_id = p.ID  AND
            pm3.meta_key    = 'third_field_key'
    WHERE   post_status = 'publish'

Seems to work. It trips up if you use any of those meta fields in a way that allows multiple meta values for it on the same post. I can't think of a join to do that.

So, question 1: Is there a join, sub-query, or whatever, to bring in multiple-value meta fields?

But question 2: Is it worth it? How many postmeta table joins do I add before a 2-query approach becomes preferable? I could grab all post data in one query, then grab all relevant postmeta in another, and combine the meta with the post data in one resultset in PHP. Would that end up being quicker than an single ever-more-complex SQL query, if that's even possible?

I always think, "Give as much work as possible to the database." Not sure on this one!

  • I'm not sure if you even want to do the joins. the combination of get_posts() and get_post_meta() gives you the same data back. In fact, it's less efficient using the joins since you might be retrieving data you won't use later.
    – rexposadas
    Jan 10, 2012 at 23:00
  • 2
    Isn't post meta data cached automatically anyway? Jan 11, 2012 at 1:13
  • @rxn, if I have several hundred posts coming back (they're a custom post type), surely it's quite a heavy DB load to get_posts(), then get_post_meta() for every one of those? @MannyFleurmond, it's hard to find hard info on WP's built-in caching, but AFAIK it would cache stuff per request. The call to the server to grab this data is an AJAX call, and I don't think anything else will be grabbing stuff before it. Jan 11, 2012 at 13:01
  • Actually, I'm going for multiple queries and caching the results. It turns out we not only need post meta, including fields that have multiple values, we also need data on users connected to the posts via meta fields (two sets of these), plus user meta on them. Pure SQL is definitely out of the window! Jan 11, 2012 at 14:20

8 Answers 8


Post meta information is automatically cached in memory for a standard WP_Query (and the main query), unless you specifically tell it not to do so by using the update_post_meta_cache parameter.

Therefore, you should not be writing your own queries for this.

How the meta caching works for normal queries:

If the update_post_meta_cache parameter to the WP_Query is not set to false, then after the posts are retrieved from the DB, then the update_post_caches() function will be called, which in turn calls update_postmeta_cache().

The update_postmeta_cache() function is a wrapper for update_meta_cache(), and it essentially calls a simple SELECT with all the ID's of the posts retrieved. This will have it get all the postmeta, for all the posts in the query, and save that data in the object cache (using wp_cache_add()).

When you do something like get_post_custom(), it's checking that object cache first. So it's not making extra queries to get the post meta at this point. If you've gotten the post in a WP_Query, then the meta is already in memory and it gets it straight from there.

Advantages here are many times greater than making a complex query, but the greatest advantage comes from using the object cache. If you use a persistent memory caching solution like XCache or memcached or APC or something like that, and have a plugin that can tie your object cache to it (W3 Total Cache, for example), then your whole object cache is stored in fast memory already. In which case, there's zero queries necessary to retrieve your data; it's already in memory. Persistent object caching is awesome in many respects.

In other words, your query is probably loads and loads slower than using a proper query and a simple persistent memory solution. Use the normal WP_Query. Save yourself some effort.

Additional: update_meta_cache() is smart, BTW. It won't retrieve meta information for posts that already have their meta information cached. It doesn't get the same meta twice, basically. Super efficient.

Additional additional: "Give as much work as possible to the database."... No, this is the web. Different rules apply. In general, you always want to give as little work as possible to the database, if it's feasible. Databases are slow or poorly configured (if you didn't configure it specifically, you can bet good money that this is true). Often they are shared among many sites, and overloaded to some degree. Usually you have more web servers than databases. In general, you want to just get the data you want out of the DB as fast and simply as possible, then do the sorting out of it using the web-server-side code. As a general principle, of course, different cases are all different.


I would recommend a pivot query. Using your example:

SELECT  p.ID,   
        MAX(CASE WHEN pm1.meta_key = 'first_field' then pm1.meta_value ELSE NULL END) as first_field,
        MAX(CASE WHEN pm1.meta_key = 'second_field' then pm1.meta_value ELSE NULL END) as second_field,
        MAX(CASE WHEN pm1.meta_key = 'third_field' then pm1.meta_value ELSE NULL END) as third_field,

 FROM    wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)                      
  • This answer should be marked correct.
    – Luke
    Mar 13, 2012 at 6:59
  • If you are looking for a database query this is the correct answer Apr 8, 2015 at 22:34
  • This query reduced my time when I was using WP_Query from ~25 secs to ~3 secs. My requirement was to fire this only once so no caching was needed.
    – Kush
    Jun 3, 2018 at 15:58
  • What en elegant query. Left joins didn't work in my case because they're pretty slow … this helped me exporting thousands of posts in under a second. +1
    – GDY
    Dec 1, 2021 at 12:29

I've come across a case where I want also want to quickly retrieve lots of posts with their associated meta information. I need to retrieve O(2000) posts.

I tried it using Otto's suggestion - running WP_Query::query for all posts, and then looping through and running get_post_custom for each post. This took, on average, about 3 seconds to complete.

I then tried Ethan's pivot query (though I didn't like having to manually ask for each meta_key I was interested in). I still had to loop through all retrieved posts to unserialize the meta_value. This took, on average, about 1.3 seconds to complete.

I then tried using the GROUP_CONCAT function, and found the best result. Here's the code:

global $wpdb;
$wpdb->query('SET SESSION group_concat_max_len = 10000'); // necessary to get more than 1024 characters in the GROUP_CONCAT columns below
$query = "
    SELECT p.*, 
    GROUP_CONCAT(pm.meta_key ORDER BY pm.meta_key DESC SEPARATOR '||') as meta_keys, 
    GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key DESC SEPARATOR '||') as meta_values 
    FROM $wpdb->posts p 
    LEFT JOIN $wpdb->postmeta pm on pm.post_id = p.ID 
    WHERE p.post_type = 'product' and p.post_status = 'publish' 

$products = $wpdb->get_results($query);

// massages the products to have a member ->meta with the unserialized values as expected
function massage($a){
    $a->meta = array_combine(explode('||',$a->meta_keys),array_map('maybe_unserialize',explode('||',$a->meta_values)));
    return $a;

$products = array_map('massage',$products);

This took on average 0.7 seconds. That's about a quarter of the time of the WP get_post_custom() solution and about half of the pivot query solution.

Maybe this will be of interest to someone.

  • I would be interested in what results you get with a persistent object cache solution. The object cache will sometimes be slower for the base case, depending on your database and configuration, but real world results with a majority of hosts will give widely varying results. Memory based caching is ridiculously fast.
    – Otto
    Oct 13, 2012 at 3:07
  • Hey @Otto. Regardless of which method I use to get the data, I definitely want to cache the result. I've tried using the transient API to do it, but I'm hitting memory problems. The serialized string for my 2000 objects clocks at ~8M and set_transient() fails (memory exhausted). Also, have to change the max_allowed_packet MySQL setting. I'll look into caching it to file, but I'm not sure yet of the the performance there. Is there a way to cache to memory that persists across requests? Oct 29, 2012 at 20:52
  • Yes, if you have a persistent memory cache (XCache, memcached, APC, etc), and use an object caching plugin (W3 Total Cache supports many types of memory caches), then it stores all the object cache in memory, giving you a many-fold speedup of pretty much everything.
    – Otto
    Nov 10, 2012 at 17:09
  • I am returning 6000 items to use in a backbone/underscore js filtering scheme. This took a 6s custom query that I couldn't even run as a WP_Query because it timed out, and made it a 2s query. Although the array_map slows it back down quite a bit...
    – Jake
    Dec 14, 2013 at 19:51
  • Is there any support for building high performance support for returning all meta data within a WP_Query?
    – atwellpub
    Sep 12, 2014 at 21:45

I found myself in a situation that I needed to do this task to ultimately create a CSV document from, I ended up working directly with mysql to do this. My code joins the post and meta tables to retrieve woocommerce pricing information, the previously posted solution required that I use table aliases in the sql to work properly.

SELECT p.ID, p.post_title, 
    MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
    MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
    MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
    MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
    FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)                 
    WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
    GROUP BY p.ID, p.post_title

Do be warned though, woocommerce created 300K+ rows in my meta table, so it was very large, and therefore very slow.



Get all posts and all of their meta values (metas) with no SQL:

Let's say you have a list of post IDs stored as an array of IDs, something like

$post_ids_list = [584, 21, 1, 4, ...];

Now getting all posts and all metas in 1 query is not possible without using at least a bit of SQL, so we must do 2 queries (still just 2):

1. Get all the posts ( using WP_Query )

$request = new WP Query([
  'post__in' => $post_ids_list,
  'ignore_sticky_posts' => true, //if you want to ignore the "stickiness"

(Don't forget to call wp_reset_postdata(); if you are doing a "loop" afterwards ;) )

2. Update meta cache

//don't be confused here: "post" means content type (post X user X ...), NOT post type ;)
update_meta_cache('post', $post_ids_list);

To get the meta data just use the standard get_post_meta() which, as @Otto pointed out:
looks into cache first :)

Note: If you don't actually need other data from the posts (like title, content, ... ) you can do just 2. :-)


using the solution form trevor and modifying it to work with nested SQL. This is not tested.

global $wpdb;
$query = "
    SELECT p.*, (select pm.* From $wpdb->postmeta AS pm WHERE pm.post_id = p.ID)
    FROM $wpdb->posts p 
    WHERE p.post_type = 'product' and p.post_status = 'publish' 
$products = $wpdb->get_results($query);

this question was asked about 12 years ago but I ran into this problem just recently when i had to search for post types with a certain criteria more complex than what can be done with a base WP_Query. I used the code below to do something very similar.

SELECT p.ID,p.post_title,pm1.meta_value as lat,pm2.meta_value as lng,pm3.meta_value as city,pm4.meta_value as state,pm5.meta_value as zip
FROM `wp_posts` p
LEFT JOIN `wp_postmeta` pm1 ON pm1.post_id = p.ID AND pm1.meta_key = '_lat'
LEFT JOIN `wp_postmeta` pm2 ON pm2.post_id = p.ID AND pm2.meta_key = '_lng'
LEFT JOIN `wp_postmeta` pm3 ON pm3.post_id = p.ID AND pm3.meta_key = '_city'
LEFT JOIN `wp_postmeta` pm4 ON pm4.post_id = p.ID AND pm4.meta_key = '_state'
LEFT JOIN `wp_postmeta` pm5 ON pm5.post_id = p.ID AND pm5.meta_key = '_zip'
WHERE post_type = "{posttype}" and post_status = "publish";

Other answers on this question try something different than just using LEFT JOIN like the author originally did. Through some testing it does seem to be the fastest way of getting the data.

SELECT  p.ID,   
MAX(CASE WHEN pm1.meta_key = '_lat' then pm1.meta_value ELSE NULL END) as lat,
MAX(CASE WHEN pm1.meta_key = '_lng' then pm1.meta_value ELSE NULL END) as lng,
MAX(CASE WHEN pm1.meta_key = '_city' then pm1.meta_value ELSE NULL END) as city,
MAX(CASE WHEN pm1.meta_key = '_state' then pm1.meta_value ELSE NULL END) as state,
MAX(CASE WHEN pm1.meta_key = '_zip' then pm1.meta_value ELSE NULL END) as zip
    wp_posts p LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID WHERE p.post_type = '{posttype}' AND p.post_status = 'publish'                     

This query is very similar to another answer on this post by Terry and Ethan and took .0228 seconds to complete. while the first query took about .0034 seconds to complete or about 6.7x faster.

This was done on a database with only 322 posts but when tested without limiting it to the custom post type and querying 5234 posts the query above got a time of 0.2408s and the first query got a time of 0.0029s or about 83x faster. Im not sure how it was faster with 5234 posts vs 332 posts (i tested it twice to be sure).

TLDR: For anybody looking for the fastest solution in 2024 with a default wordpress setup just using the LEFT JOIN query above seems to be the fastest solution.


I ran into the multiple value meta fields problem as well. The problem is with WordPress itself. Look in wp-includes/meta.php. Look for this line:

$where[$k] = ' (' . $where[$k] . $wpdb->prepare( "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$meta_compare_string})", $meta_value );

The problem is with the CAST statement. In a query for meta values, the $meta_type variable is set to CHAR. I don't know the details on how CASTing the value to CHAR affects the serialized string, but to fix it, you can remove the cast so the SQL looks like this:

$where[$k] = ' (' . $where[$k] . $wpdb->prepare( "$alias.meta_value {$meta_compare} {$meta_compare_string})", $meta_value );

Now, even though that works, you're mucking with the WordPress internals, so other things might break, and it's not a permanent fix, assuming you'll need to upgrade WordPress.

The way I've fixed it is to copy the SQL generated by WordPress for the meta query I want and then write some PHP to tack on extra AND statements for the meta_values I'm looking for and use $wpdb->get_results($sql) for the final output. Hacky, but it works.

  • I've not tried it, but leveraging the get_meta_sql filter that follows this line would of course be preferable to hacking core code. Apr 25, 2012 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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