0

I'm trying to build a directory of members that has some search options at the top. Each member has several custom fields as well (using Advanced Custom Fields). Would like to have a single query that gathers all the data at once, including all the custom fields for each member - instead of just querying the posts and looping through and having to query the custom fields (100 show at a time - each with 10 custom fields).

We've gotten pretty close, the below query does work and returns all the necessary data in clean rows. It's painfully slow however, ~10 seconds, which we know is coming from the ORDERBY clause. This query has gotten a bit over my head though and I can't figure out how to optimize it run faster. Note that the orderby fields are coming from wp_postmeta, so there is no way just to add an index to the 'member_last_name' field for instance.

Any ideas???

THANKS!!!

Philip

SELECT * FROM ( 
  SELECT 
    MAX(CASE WHEN wp_postmeta.meta_key = 'member_first_name' THEN wp_postmeta.meta_value END ) AS 'member_first_name', 
    MAX( CASE WHEN wp_postmeta.meta_key = 'member_last_name' THEN wp_postmeta.meta_value END ) AS 'member_last_name',
    wp_posts.ID FROM wp_posts 
  LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
  WHERE wp_posts.post_status = 'publish' 
  AND wp_posts.post_type = 'member' 
  GROUP BY wp_posts.ID 
  ORDER BY member_last_name desc 
  LIMIT 5 
) AS t;
  • Have you tried replacing the LEFT JOIN with a plain join, eg INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key IN ('member_first_name', 'member_last_name'))? Don't get the reason for the nesting btw. – bonger Jul 30 '14 at 17:06
  • Thanks bonger, INNER JOIN had no effect I'm afraid. The nesting is just as close as I've gotten anyway based on reading other posts. The goal is just to get 100 posts with all their meta data as quickly as possible. Was trying to avoid a bunch of queries in a big loop. – Philip Light Jul 31 '14 at 17:19
  • But those figures are peanuts to a database! I just tried a very similar query (nice MAX() technique btw) returning 10 meta fields on 74 posts in around 4 microseconds. Weird... – bonger Jul 31 '14 at 19:40
-1

Why not just use WP_Query for this instead of inventing the wheel all over again? I'm pretty sure you can solve this with a meta query within your WP_Query. See the codex.

  • Please explain why this solves the problem. And maybe add an example on how to use it. This prevents your answers from being flagged as low quality. – kaiser Jul 30 '14 at 21:29
  • Thanks for the reply tormorten. As far as I know though, you can only query by the meta fields, selecting those that match a criteria. We are attempting to pull the meta fields however in the same results as the post. The hope is, this can run much faster than querying the meta fields for each post in a loop of 100. – Philip Light Jul 31 '14 at 17:14
  • He's right though, except about using a meta query. If you just do a plain query for your posts, WP will do two extra queries, one to cache the taxonomy data for all posts selected, and one to cache the postmeta data for all posts selected. No extra queries for meta fields will occur in your loop. – bonger Jul 31 '14 at 19:37

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.