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.
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;