CODEWITHSUNDEEP
phpmysqlsqlquery-optimization
Phil Young
Phil Young

Reputation: 1354

MySQL time interval query overloading server

I am generating a CSV of how many orders I have every month for the last 12 months per item. At the moment I am retrieving the items and using a foreach loop to submit the following query for each item, in order to retrieve the total times it has been ordered in each month over the past 12 months. However, the query takes just over 3 seconds, so when the loop goes a couple of thousand times, it causes the MySQL server to go away.

How could I optimise this query? Should the load on the database be reduced if I use a sub query instead?

Here is the query(which was adapted from here):

SELECT order_item_variant_alias_id, DATE(DATE_FORMAT(order_progress_time, '%Y-%m-01')) AS `trueMonth`, COUNT(*) AS count
FROM tbl_order_progress
JOIN tbl_order_items ON order_progress_order_id = order_item_order_id
JOIN tbl_product_variant_aliases ON order_item_variant_alias_id = product_variant_alias_id
JOIN tbl_product_variants ON product_variant_id = product_variant_alias_variant_id
GROUP BY product_variant_alias_id, DATE(DATE_FORMAT(order_progress_time, '%Y-%m-01'))
HAVING order_item_variant_alias_id = 1

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Why are you running a separate query for each item? Just drop the having clause and put all items in at the same time.

If, for some reason, you do need to do one item at a time, switch the logic to a where clause rather than a having clause. The having clause will aggregate all items and then filter down to the one you want. MySQL should be faster if you reduce the data first -- using where:

SELECT order_item_variant_alias_id,
       DATE(DATE_FORMAT(order_progress_time, '%Y-%m-01')) AS `trueMonth`,
       COUNT(*) AS count
FROM tbl_order_progress JOIN
     tbl_order_items
     ON order_progress_order_id = order_item_order_id JOIN 
     tbl_product_variant_aliases
     ON order_item_variant_alias_id = product_variant_alias_id JOIN
     tbl_product_variants
     ON product_variant_id = product_variant_alias_variant_id
WHERE order_item_variant_alias_id = 1
GROUP BY product_variant_alias_id,
         DATE(DATE_FORMAT(order_progress_time, '%Y-%m-01'));

Upvotes: 1

Related Questions