Optimize Query - Select COUNT and SUM from the same subquery-CodePudding

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;

CodePudding user response：

The problem with your query is that you have nested correlated sub queries. Correlated sub queries are a huge hit to SQL performance. In this case you want to try use joins with aggregation (group by).

I don't understand the intricacies of the schema your database, so I'm not quite sure why you're limiting the sub queries for firstName etc to 1 record. I can edit the answer to accommodate that if you give more detail.

I would suggest you try something like this:

SELECT   u.ID,
         u.user_email AS mail, 
         u.user_login AS userName, 
         u.user_registered  AS signUpDate, 
         mfn.meta_value as firstName, 
         mln.meta_value as lastName, 
         mbp.meta_value as billingPhone, 
         msp.meta_value as shippingPhone, 
         COUNT(pval.meta_value) as orderCount,
         SUM(pval.meta_value) as moneySpent 
FROM     wp_users u
JOIN     wp_usermeta as mfn  ON u.ID = mfn.user_id  AND mfn.meta_key = 'first_name'
JOIN     wp_usermeta as mln  ON u.ID = mln.user_id  AND mln.meta_key = 'last_name'
JOIN     wp_usermeta as mbp  ON u.ID = mbp.user_id  AND mbp.meta_key = 'billing_phone'
JOIN     wp_usermeta as msp  ON u.ID = msp.user_id  AND msp.meta_key = 'shipping_phone'
JOIN     wp_postmeta as p    ON p.meta_value = u.ID AND p.meta_key = '_customer_user'
JOIN     wp_postmeta as pval ON pval.post_id = p.post_id AND pval.meta_key = '_order_total'
GROUP BY u.ID,
         u.user_email, 
         u.user_login, 
         u.user_registered, 
         mfn.meta_value, 
         mln.meta_value, 
         mbp.meta_value, 
         msp.meta_value

As per @O.Jones's comment, consider using LEFT JOINS instead of INNER JOINS so that the query still returns results where a meta_value is missing.