trick to get the sum of MySQL rows outside of MySQL

so I have the following query:

SELECT DISTINCT d.iID1 as 'id',
            开发者_C百科          SUM(d.sum + d.count*r.lp)/sum(d.count) AS avgrat
                      FROM abcd i, abce r, abcf d
                      WHERE r.aID = 1 AND
                      d.iID1 <> r.rID  AND d.iID2 = r.rID GROUP BY d.iID1
                      ORDER BY avgrat LIMIT 50;

the problem is....with millions of entries in the table, SUM() and GROUP BY would freeze up the query....is there a way to do exactly this that would execute instantaneously using MySQL and/or PHP hacks (perhaps do the summing with PHP....but how would I go about doing that...)

To answer the direct question: no, there is no way to do anything instantaneously.

If you have control over the table updates, or the application which adds the relevant records, then you could add logic which updates another table with the sum, count, and id with each update. Then a revised query targets the "sum table" and trivially calculates the averages.

One solution is to create a rollup table that holds your aggregate values using a triggers on your source tables to keep it up to date.

You will need to decide if the overhead of the triggers is less then that of the query. some important factors are:

The frequency of the source table updates

The run frequency of the aggregate query.