Your current query structure cannot work! Your query returns when you have
customers.total_orders = 1 because none of the left joins returns a set.
As an example take one customer with 10 orders but only one containing the desired product. 1st three tables return 1 row, 1st LEFT JOIN returns 9 rows, 2nd 8 rows, 3rd 7 rows, 4th 6 rows and 5th 5 rows. That’s a set of 9x8x7x6x5=15,120 (before applying GROUP BY) for 1 customer. If customer placed 20 orders it would be 1,395,360 rows. Adding an index to total_orders will not fix this.
In your question you asked –
if a customer ordered X product, what is their lifetime value
so the solution is to find all customers who have ordered
product X and then join back to orders to find their lifetime value –
SELECT c1.*, COUNT(o.id) AS num_orders, SUM(o.mc_gross) AS orders_total, MIN(o.purchased_at) AS first_order, MAX(o.purchased_at) AS last_order, IF(COUNT(*) = 1, 0, ROUND(TIMESTAMPDIFF(DAY, MIN(o.purchased_at), MAX(o.purchased_at)) / (COUNT(o.id) - 1))) AS avg_between_orders FROM ( SELECT customers.id, customers.name FROM customers INNER JOIN orders o ON o.customer_id = customers.id AND o.store_id = 10 INNER JOIN order_items oi ON oi.order_id = o.id AND oi.product_variant_id = 1 # THE INNER JOIN to orders renders this WHERE clause redundant # WHERE customers.total_orders >= 1 WHERE customers.created_at >= CURDATE() - INTERVAL 365 DAY GROUP BY customers.id ) AS c1 INNER JOIN orders o ON c1.id = o.customer_id GROUP BY c1.id
My tiny test dataset (3 customers, 13 orders and 3 order_items)shows 36,134 rows examined for your query but 16 rows examined with my query.