+- 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: Speed up Queries (/showthread.php?tid=51950)
Speed up Queries - El Forum - 05-24-2012
[eluser]Near[/eluser]
hi guys! someone please help me on how to speed up my queries. other than using database cache. thanks!
Speed up Queries - El Forum - 05-24-2012
[eluser]Near[/eluser]
example of query
Code:
SELECT * FROM ( SELECT mc_match.*,users.i_gender,user.gender,qn_category.ca_name,qn_category.ca_level FROM m_match LEFT JOIN users ON users.user_id=m_match.user_id LEFT JOIN qn_category ON m_match.ca_id=qn_category.ca_id WHERE user.user_terminated=0 )as match_table WHERE match_table.user_id ='2' GROUP BY match_table.ca_idthis query checks almost 2000 records,, thanks
Speed up Queries - El Forum - 05-24-2012
[eluser]InsiteFX[/eluser]
Do not use the * select only the fields you need.
Speed up Queries - El Forum - 05-24-2012
[eluser]CroNiX[/eluser]
Make sure everything is properly indexed for faster lookups.
Speed up Queries - El Forum - 05-24-2012
[eluser]Near[/eluser]
unfortunately i will use all the data on the table thats why is use the SELECT * , the page took 25 sec on loading...
Speed up Queries - El Forum - 05-24-2012
[eluser]InsiteFX[/eluser]
Do not use the * specify every field MySQL will find them faster!
After you've identified the slow queries you should learn about the MySQL internal tools, like EXPLAIN, SHOW STATUS, and SHOW PROCESSLIST. These will tell you what resources are being spent where, and what side effects your queries are having, e.g., whether your heinous triple-join subselect query is sorting in memory or on disk. Of course, you should also be using your usual array of command-line profiling tools like top, procinfo, vmstat, etc. to get more general system performance information.
Speed up Queries - El Forum - 05-24-2012
[eluser]Near[/eluser]
is it possible if i use cache driver? to help the page load a little bit faster? but the content of page is dynamic.
Speed up Queries - El Forum - 05-24-2012
[eluser]Abel A.[/eluser]
Find another way get your data, nested subqueries are too slow.
Speed up Queries - El Forum - 05-25-2012
[eluser]weboap[/eluser]
check in:
config/database.php
Code:
$db['default']['cache_on'] = TRUE;
$db['default']['cachedir'] = 'your_cache_path';don't forget to implement a clear cache mechanism when the db is updated...