0

I need to collect several tables for posts, and this is currently my query:

$result = $wpdb->get_results( "
  SELECT
    p.ID            AS id,
    pm.meta_value   AS text,
    pm2.meta_value  AS stock_status,
    pm3.meta_value  AS stock,
    pm4.meta_value  AS units_per_package,
    pm5.meta_value  AS aaa_price,
    pm6.meta_value  AS bbb_price,
    pm7.meta_value  AS ccc_price,
    pm8.meta_value  AS ddd_price,
    pm9.meta_value  AS exp_date,
    pm10.meta_value AS del_option
  FROM {$wpdb->prefix}posts p
  LEFT JOIN {$wpdb->prefix}postmeta pm   ON pm.post_id   = p.ID AND pm.meta_key   = '_sku'
  LEFT JOIN {$wpdb->prefix}postmeta pm2  ON pm2.post_id  = p.ID AND pm2.meta_key  = '_stock_status'
  LEFT JOIN {$wpdb->prefix}postmeta pm3  ON pm3.post_id  = p.ID AND pm3.meta_key  = '_stock'
  LEFT JOIN {$wpdb->prefix}postmeta pm4  ON pm4.post_id  = p.ID AND pm4.meta_key  = '__quantity'
  LEFT JOIN {$wpdb->prefix}postmeta pm5  ON pm5.post_id  = p.ID AND pm5.meta_key  = 'aaa_price'
  LEFT JOIN {$wpdb->prefix}postmeta pm6  ON pm6.post_id  = p.ID AND pm6.meta_key  = 'bbb_price'
  LEFT JOIN {$wpdb->prefix}postmeta pm7  ON pm7.post_id  = p.ID AND pm7.meta_key  = 'ccc_price'
  LEFT JOIN {$wpdb->prefix}postmeta pm8  ON pm8.post_id  = p.ID AND pm8.meta_key  = 'ddd_price'
  LEFT JOIN {$wpdb->prefix}postmeta pm9  ON pm9.post_id  = p.ID AND pm9.meta_key  = 'exp_date'
  LEFT JOIN {$wpdb->prefix}postmeta pm10 ON pm10.post_id = p.ID AND pm10.meta_key = '_del_option'
  WHERE
    -- pm2.meta_value = 'instock' AND
    p.post_type = 'product' AND
    p.post_status = 'publish'
", 'ARRAY_A');

The problem is that this is a very very slow query, but I'm not sure there's any other way to speed this up. Is there a method I'm not seeing or not using that could take this query from 0.64s to something much much faster?

From this point, I use $result to loop through and format the values returned, then build up an array of IDs and their respective meta.

  • 1
    Why don't you just fetch all meta values? And ARRAY_A is a constant, not a string. – fuxia May 12 '17 at 9:02
  • Wouldn't this be as slow? Lets say there are 40 meta values in total per post, and this is collecting 10 posts, would that it's fetching the additional 29 meta, is that going to change anything? – Andrew Lazarus May 12 '17 at 9:05
  • Nah, that's not slow. And in any case, it is much, much faster than all these JOINs. Try it, compare the time. – fuxia May 12 '17 at 9:05
  • Querying for posts by their post meta is always going to be slow, it's not what those tables were intended for. That's why we have taxonomies and terms with dedicated tables. Those are built and optimised for searching grouping and filtering. A custom taxonomy and a WP_Query looking for posts in the term instock of type product would be much easier to type, and orders of magnitude faster. Also, Core already fetches all post meta at once and caches it when it fetches a post – Tom J Nowell May 12 '17 at 9:57
  • @TomJNowell can you help point me towards something that would replace this? documentation on the caching of the meta etc or get fetching it? get_post_meta()? – Andrew Lazarus May 12 '17 at 13:37

Your Answer

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

Browse other questions tagged or ask your own question.