Hi,
I found that when i browse the list page, it was very very slow, more than 10 sec waiting.
So i installed monyog at http://www.webyog.com/en/ to monitor slow SQL query.
I found that the resulted SQL produced by phprunner generated page with master - child
with record counts have slowed down the response significantly.
Here's what produced by phprunner generated page for the SQL Query with child record counts:
SELECT
id, msg_from, msg_to, msg_text, id_client, date_scheduled, status, date_status,
(SELECT count() FROM (SELECT job_id FROM jobs_details_sent WHERE status_code = "status=0" AND deleted = 0 ) subQuery_cnt WHERE subQuery_cnt.job_id=jobs_schedule.id) as S_cnt
FROM jobs_schedule where (service_flag = 1 AND deleted = 0 ) and ( id_client = 223) ORDER BY 5 DESC limit 0,15;
It took 13 secs to produce 15 rows results when i executed the SQL manually.
As show above, the child records with count are identified as follows:
(SELECT count() FROM (SELECT job_id FROM jobs_details_sent WHERE status_code = "status=0" AND deleted = 0 ) subQuery_cnt WHERE subQuery_cnt.job_id=jobs_schedule.id ) as S_cnt
Above is the subquery formed by phprunner to produce record counts.
I tried to modify the SQL above to:
(SELECT count() FROM jobs_details_sent WHERE status_code = "status=0" AND deleted = 0 and job_id=jobs_schedule.id) as S_cnt
and the original query becomes:
SELECT
id, msg_from, msg_to, id_client, date_scheduled, status, date_status,
(SELECT count() FROM jobs_details_sent WHERE status_code = "status=0" AND deleted = 0 and job_id=jobs_schedule.id) as S_cnt
FROM jobs_schedule where (service_flag = 1 AND deleted = 0 ) and ( id_client = 223) ORDER BY 5 DESC limit 0,15;
i manually executed the query and it took only 0.094 sec to produce the same result.
Conclusion: It's 138% improved in performance if the SQL Query for record count is re-designed! Is it possible to look at how the records count are generated in phprunner in master-child tables and optimize it like above?
it makes a lot of difference, the current method has made the page too slow.
Thanks
acp