Optimize Query - Select COUNT and SUM from the same subquery

Question

I tried to find the solution to this on Stackoverflow, maybe my wording is wrong.

I have a query which takes to long to execute. I am sure there are simple ways to improve it. For example, I use the same sub-query twice for displaying two different columns (sum and count) but encountered several errors while trying to solve it on my own.

SELECT u.ID,
u.user_email AS mail, 
u.user_login AS userName, 
u.user_registered  AS signUpDate, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'first_name' limit 1) as firstName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'last_name' limit 1) as lastName, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'billing_phone' limit 1) as billingPhone, 
(select meta_value from wp_usermeta where user_id = u.ID and meta_key = 'shipping_phone' limit 1) as shippingPhone, 
(SELECT COUNT(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as orderCount,
(SELECT SUM(meta_value) from wp_postmeta WHERE meta_key = '_order_total' and post_id IN (select post_id from wp_postmeta where meta_value = u.ID and meta_key = '_customer_user')) as moneySpent 
FROM wp_users u;

Answer 1

@Timur, as per your question in the comments of the first answer; you'd have to do something like this. You still need joins but you don't need as many for the wp_usermeta table. You will still need to join to wp_postmeta twice, because you can't do a join on the '_customer_user' meta_key and simultaneously retrieve the '_order_total' meta_key in a single join.

Just a note on the MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value END) logic; it's the equivalent to MAX(CASE WHEN um.meta_key = '...' THEN um.meta_value ELSE NULL END).

SELECT    u.ID, 
          u.user_email AS mail,
          u.user_login AS userName,
          u.user_registered AS signUpDate,
          MAX(CASE WHEN um.meta_key = 'first_name'     THEN um.meta_value END) AS firstName, 
          MAX(CASE WHEN um.meta_key = 'last_name'      THEN um.meta_value END) AS lastName, 
          MAX(CASE WHEN um.meta_key = 'billing_phone'  THEN um.meta_value END) AS billingPhone, 
          MAX(CASE WHEN um.meta_key = 'shipping_phone' THEN um.meta_value END) AS shippingPhone,
          COUNT(pval.meta_value) AS orderCount,
          SUM(pval.meta_value) AS moneySpent
FROM      wp_users AS u
LEFT JOIN wp_usermeta AS um   ON u.ID = um.user_id
LEFT JOIN wp_postmeta AS pm   ON u.ID = pm.meta_value      AND pm.meta_key = '_customer_user'
LEFT JOIN wp_postmeta AS pval ON pm.post_id = pval.post_id AND pval.meta_key = '_order_total'
WHERE     (um.meta_key IN ('first_name', 'last_name', 'billing_phone', 'shipping_phone') 
           OR um.meta_key IS NULL)
GROUP BY  u.ID, 
          u.user_email,
          u.user_login,
          u.user_registered;

I'm not 100% sure on the logic. Let me know if it needs tweaking