Speed up Queries

[eluser]Near[/eluser]
hi guys! someone please help me on how to speed up my queries. other than using database cache. thanks!

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

this query checks almost 2000 records,, thanks

[eluser]InsiteFX[/eluser]
Do not use the * select only the fields you need.

[eluser]CroNiX[/eluser]
Make sure everything is properly indexed for faster lookups.

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

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

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

[eluser]Abel A.[/eluser]
Find another way get your data, nested subqueries are too slow.

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