0.5s query takes 13s to run 4 times

[eluser]sigsauer[/eluser]
I'm looping a query to fetch price history for 4 different items.

The query takes half a second to run, but putting it in a loop to run 4 times takes too much time.

Is it because it is connecting and disconnecting to/from the database every time?

Thanks!

[eluser]sigsauer[/eluser]
the query is just slow... I checked with

$this->output->enable_profiler();

SELECT AVG(price) as price, week from globalhistory where product_id = 1 and week > 1098421200 GROUP BY week

this query takes 4 seconds to complete... not sure what to do now.

[eluser]WanWizard[/eluser]
What database engine is used? How big is this table? What platform are you running this on (hosting, dedicated server, local on a laptop, ...)?

If MySQL, run

Code:

EXPLAIN EXTENDED SELECT AVG(price) as price, week from globalhistory where product_id = 1 and week > 1098421200 GROUP BY week

And check if it uses the proper indexes.

[eluser]sigsauer[/eluser]
It's using MyISAM. The table has 7.5 million records. It's running on Rackspace Cloud's Cloud Site product.

All I can think of is on "group by" optimizations and on the indexes.

Should the engine play a big role on this query?

[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.

[eluser]sigsauer[/eluser]
I was able to bring execution time down considerably by creating an index on product_id, week and price.

Thanks for the tips!