Need Help optimizing a complex MySQL query

[-OG7-]

I have this query below. There are 4 main tables involved: tblOrder, tblItems, tblOrder_archive, tblItem_archive. Orders and Items get moved over to the archived versions of the tables after a few months as not to slow down the main table queries. (sales and traffic is REALLY HIGH). So to get sales figures, i select what i need from each set of tables (archive and non archive).. union them.. do a group by on the union.. then do some math on the result.Problem is that with any significant amount of rows (the order time span).. it will take so long for the query to run that it times out. I have added all the keys I can think of and still running super slow.Is there more I can do to make this run faster? Can i write it differently? Can i use different indexes? or should i write a script that gets the data from each table set first then does the math in the php script to combine them? Thanks for the help.\[code\]SELECT description_invoice, supplier, type, sum(quantity) AS num_sold, sum(quantity*wholesale) AS wholesale_price, sum(quantity*price) AS retail_price, sum(quantity*price) - sum(quantity*wholesale) AS profitFROM ( SELECT tblOrder.* , tblItem.description_invoice , tblItem.type , tblItem.product_number , tblItem.quantity , tblItem.wholesale , tblItem.price , tblItem.supplier FROM tblOrder USE KEY (finalized), tblItem WHERE tblItem.order_id = tblOrder.order_id AND finalized=1 AND wholesale <> 0 AND (order_time >= 1251788400 AND order_time <= 1283669999) UNION SELECT tblOrder_archive.* , tblItem_archive.description_invoice , tblItem_archive.type , tblItem_archive.product_number , tblItem_archive.quantity , tblItem_archive.wholesale , tblItem_archive.price , tblItem_archive.supplier FROM tblOrder_archive USE KEY (finalized), tblItem_archive WHERE tblItem_archive.order_id=tblOrder_archive.order_id AND finalized=1 AND wholesale <> 0 AND (order_time >= 1251788400 AND order_time <= 1283669999)) AS main_tableGROUP BY description_invoice, supplier,type ORDER BY profit DESC;\[/code\]