How do I make this MySQL query faster and better?

by

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.

More Related Contents:

  1. MySQL: Return 2 different result together
  2. Get top n records for each group of grouped results
  3. need to return two sets of data with two different where clauses
  4. quick selection of a random row from a large table in mysql
  5. Join tables with SUM issue in MYSQL
  6. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  7. Return default result for IN value regardless
  8. How can I search (case-insensitive) in a column using LIKE wildcard?
  9. Search for “whole word match” in MySQL
  10. sql joins as venn diagram
  11. How can I join multiple SQL tables using the IDs?
  12. MySQL, better to insert NULL or empty string?
  13. SQL: Repeat a result row multiple times, and number the rows
  14. SQL: selecting rows where column value changed from previous row
  15. Why do results from a SQL query not come back in the order I expect?
  16. How to generate data in MySQL?
  17. MySQL combine two columns and add into a new column
  18. Invalid default value for ‘dateAdded’
  19. MySQL error 1241: Operand should contain 1 column(s)
  20. How to set a MySQL row to READ-ONLY?
  21. How to create dummy variable columns for thousands of categories in Google BigQuery?
  22. Mysql: Order by like?
  23. Most efficient way to store IP Address in MySQL [duplicate]
  24. Getting first day of the week in MySql using Week No
  25. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  26. MySQL OPTIMIZE all tables?
  27. Strip last two characters of a column in MySQL
  28. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment