How do I make this MySQL query faster and better?

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 –

    COUNT( 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( - 1))) AS avg_between_orders
    FROM customers
    INNER JOIN orders o
        ON o.customer_id = AND o.store_id = 10
    INNER JOIN order_items oi
        ON oi.order_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
) AS c1
INNER JOIN orders o ON = o.customer_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.

Browse More Popular Posts

Leave a Comment