+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: 0.5s query takes 13s to run 4 times (/showthread.php?tid=32825)
0.5s query takes 13s to run 4 times - El Forum - 08-06-2010
[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!
0.5s query takes 13s to run 4 times - El Forum - 08-06-2010
[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.
0.5s query takes 13s to run 4 times - El Forum - 08-06-2010
[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 weekAnd check if it uses the proper indexes.
0.5s query takes 13s to run 4 times - El Forum - 08-06-2010
[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?
0.5s query takes 13s to run 4 times - El Forum - 08-06-2010
[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.
0.5s query takes 13s to run 4 times - El Forum - 08-09-2010
[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!