Optimizing Query that manipulate >100,000 records

Need help speeding page that takes 5 minutes to load

[jGAinnov]

Hi,
I currently have a table that has over 100,000 sales record. The page displays totals by customer, product than thickness. I have one query that read all these sums from the table into a temporary table. The temporary table is then sorted and displayed to the user.

Currently it takes an average of 280 seconds to generate all the sums.

This is the query:
SELECT c.CusName , t.thickness , t.product , (SELECT SUM(dollars) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) AND s.product=t.product AND s.thickness=t.thickness) AS s2007 , (SELECT SUM(dollars) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) AND s.thickness=t.thickness ) AS sTotal_2007, (SELECT SUM(dollars) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) ) AS sCTotal_2007 , (SELECT SUM(SQFT) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) AND s.product=t.product AND s.thickness=t.thickness) AS f2007 , (SELECT SUM(SQFT) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) AND s.thickness=t.thickness ) AS fTotal_2007, (SELECT SUM(SQFT) FROM sp_a as s USE INDEX(cust_id) WHERE s.cust_id=t.cust_id AND s.year=2007 AND month IN (1,2,3,4) ) AS fCTotal_2007 FROM customer_a as c USE INDEX(CusNum) RIGHT JOIN sp_a AS t USE INDEX(year) ON c.CusNum=t.cust_id WHERE year =2007 AND month IN (1,2,3,4) GROUP BY t.cust_id , t.thickness , t.product

Is there some other way of generating these sums? I've tried separating out the queries but it seems to slow down the page even more.

Any ideas?

[physics]

That is a bear of a query. Did you try using EXPLAIN SELECT ...

With those subqueries I'm not sure the indexes are helping you:
[bugs.mysql.com...]

There are some tips in that bug discussion regarding using SQL_BIG_RESULT, etc that you might try.

But I'm just guessing here.

[FalseDawn]

Your query looks badly constructed to me.
Forcing index use with USE INDEX is rarely a good idea, too.

If you post your table structure and an example of exactly what results you need it would help.

[syber]

Why are you generating a temporary table? Temporary tables are usually unnecessary and always cause extra I/O. Try using derived table instead, if possible.