Thread

How does one go from the slow queries log of MySQL to the template that’s the culprit?

We were getting throttled by the hosting service for slow queries and, based on a bunch of web searches I was able to delete the pagination markup in the weblog/comments template—which reduced the severity of our demands on the MySQL server to the point that they stopped throttling us.

However, there is still a steady stream of these bogus queries coming through:

SELECT COUNT(*) AS `numrows`
FROM (`exp_comments` c)
JOIN `exp_channel_titles` ct ON `ct`.`entry_id` = `c`.`entry_id`
WHERE `c`.`status` =  ‘o’
AND c.channel_id = 1
AND `ct`.`status` != ‘closed’
AND `c`.`site_id` IN (‘1’)
AND (`ct`.`expiration_date` = 0 OR `ct`.`expiration_date` > 1368640798)

We’re being throttled again and the same query is causing the problem but I’ve gone through and deleted pagination markup from everywhere I can think it might be causing the problem.

I figured out how to turn on some debugging and here is where the problem appears in the log:

...
(2.703937 / 14.58MB)    Processing Tags
(2.703971 / 14.58MB)    Module Tag: Comment/entries
(2.703989 / 14.58MB)    Module Tag: Channel/entries
(2.704006 / 14.58MB)    Module Tag: Channel/entries
(2.704024 / 14.58MB)    Module Tag: Channel/entries
(2.704041 / 14.58MB)    Module Tag: Channel/categories
(2.704062 / 14.58MB)    Including Files for Plugins and Modules
(2.704079 / 14.58MB)    Files for Plugins and Modules All Included
(2.704091 / 14.58MB)    Beginning Final Tag Data Processing
(2.704104 / 14.58MB)    Calling Class/Method: Comment/entries
(2.704285 / 14.57MB)    -> Class Called: Comment
(2.704316 / 14.57MB)    -> Method Called: entries
(18.829642 / 14.65MB)    -> Data Returned
...

So why would method “entries” of class “Comment” take such a long time?