I know, I know, I wrote this MOD but I am having an issue with the speediness of my SQL query used for gathering product/order data across multiple tables. Here is my SQL query for sales reports between Aug 1 and Aug 3 (real small timespan):
Code:
SELECT tblproducts.product, tbldetails.productid, SUM(tbldetails.price*tbldetails.amount) AS total, SUM(tbldetails.amount) AS qty_purchased FROM xcart_order_details AS tbldetails, xcart_products AS tblproducts, xcart_orders AS tblorders WHERE tblproducts.productid=tbldetails.productid AND tbldetails.orderid=tblorders.orderid AND tblorders.date >= 1091332800 AND tblorders.date <= 1091591999 AND tblorders.status = 'C' OR tblorders.status = 'P' GROUP BY tbldetails.productid ORDER BY total desc
The problem is that if I am running a sales report for a whole month or a few months, it runs SUPER slow. Any ideas on how I can optimize this query to make it run a lot faster? Thanks!