grab mysql result based on field values

Question

Hi all,

I am taking over designing a CMS from another programmer. As the site is filling up, we're finding loops in mysql queries causing long hangs. I have found a temp solution for this one, but am wondering if there is a quicker way of doing it?

take the table (tracks resources):

id resource click
1   res_1    192
2   res_2     12
3   res_3    300

what we need to get is a popularity of the resource - res_click/total_click what he had was a while loop:

while ($item = mysql_fetch_array ($result)) $total_clicks = $total_clicks + $item[0];

As there could be 100 or more resources to a page, this was running for each resource, and it is causing major hangs.

My solution is to get a sum:

SELECT SUM(click) FROM uri
SELECT click FROM resource WHERE id=$x

then divide them both. But this two calls are still running for around a 100 items per page. Is there a way I can have a field in mysql that is the result of a formula based on another another, like in excell? So I could add a field "percentage", tell mysql that it is the sum of click divided by the current click value, then every time click is updated the 'percentage' field is automatically updated?

any help would be appreciated, cheers ;)

Answer 1

you can create a view on your table that present the sum you want