I can't run any of the ubercart reports. I have captured an example of the query that is being executed.
SELECT u.uid, u.name, ou.billing_first_name, ou.billing_last_name,
(SELECT COUNT(DISTINCT(order_id)) FROM uc_orders as o WHERE o.uid = u.uid AND o.order_status IN ('completed')) as orders,
(SELECT count(qty) FROM uc_order_products as ps LEFT JOIN uc_orders as os ON ps.order_id = os.order_id WHERE os.order_status IN ('completed') AND os.uid) as products,
(SELECT SUM(ot.order_total) FROM uc_orders as ot WHERE ot.uid = u.uid AND ot.order_status IN ('completed')) as total,
ROUND((SELECT SUM(ot.order_total) FROM uc_orders as ot WHERE ot.uid = u.uid AND ot.order_status IN ('completed'))/(SELECT COUNT(DISTINCT(order_id)) FROM uc_orders as o WHERE o.uid = u.uid AND o.order_status IN ('completed')), 2) as average
FROM users as u LEFT JOIN uc_orders as ou ON u.uid = ou.uid WHERE u.uid > 0
GROUP BY u.uid
ORDER BY total DESC
LIMIT 0, 30
The section where I count the quantity, "count(qty)", is where it apparently gets hung up. I can run the rest of the query in 1700ms without issue if I remove that line.
Also, if I run the subquery all by itself (minus the where clause) it will run in 100ms.
I have 900 products, 63k orders and 85k records on the uc_orders_products table.
I have verified that fields are properly indexed.
Does anyone have any advice?
Thanks,
Matt
