Welcome Guest, Not a member yet? Register   Sign In
0.5s query takes 13s to run 4 times
#5

[eluser]WanWizard[/eluser]
The engine always plays a role. How much, that remains to be seen.

You should have an index on product_id. If not, that's the first thing you should do. If you have lots of records with the same week value, you can try an index on that two. But I guess that won't help you much. Other than that, you can't do much. The AVG() and GROUP_BY will make sure a table scan is needed, which, depending on how much records you have for the product_id, can mean a lot of data.

If this is something that needs to run regularly, I suggest you take the data warehouse approach. Run this query once a day when there is processing capacity available (p.e. at night), and store the result in a a table in a separate reports database. During the day you then run your reports on this database, which should be very fast and with low impact on performance.


Messages In This Thread
0.5s query takes 13s to run 4 times - by El Forum - 08-06-2010, 12:38 AM
0.5s query takes 13s to run 4 times - by El Forum - 08-06-2010, 01:00 AM
0.5s query takes 13s to run 4 times - by El Forum - 08-06-2010, 01:51 AM
0.5s query takes 13s to run 4 times - by El Forum - 08-06-2010, 02:16 AM
0.5s query takes 13s to run 4 times - by El Forum - 08-06-2010, 03:31 AM
0.5s query takes 13s to run 4 times - by El Forum - 08-09-2010, 03:49 AM



Theme © iAndrew 2016 - Forum software by © MyBB