Phil Gyford

Slow queries on MT home page

Vote 0 Votes

When I visit the front page of my MT install it takes a long, long time for the page to come up, if at all. I checked my MySQL processes and I see a series of queries, each of which takes a long time, and often requires "Copying to tmp table". These are, I think, all generated by the code that produces the summaries for each blog on my MT front page. Some examples:

SELECT MAX(comment_created_on) AS max_comment_created_on, comment_entry_id
FROM mt_comment, mt_entry
WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (entry_status = '2') AND (entry_blog_id = '3') AND (entry_class = 'entry') AND (entry_id =comment_entry_id)
GROUP BY comment_entry_id
ORDER BY max_comment_created_on DESC
LIMIT 100                                                                                                                                                                                                                                                              

SELECT SUM(entry_comment_count) AS sum_entry_comment_count, entry_author_id
FROM mt_entry
WHERE (entry_author_id = '1')
GROUP BY entry_author_id
ORDER BY sum_entry_comment_count DESC

SELECT COUNT(*) AS cnt, entry_blog_id
FROM mt_entry
WHERE (entry_author_id = '1') AND (entry_blog_id IN ('1','3','4','5','8','10','12','19','27','28')) AND (entry_class = 'entry')
GROUP BY entry_blog_id

SELECT COUNT(*) AS cnt, comment_blog_id
FROM mt_comment, mt_entry
WHERE (comment_blog_id IN ('1','3','4','5','8','10','12','19','27','28')) AND (entry_author_id = '1') AND (entry_id =comment_entry_id)
GROUP BY comment_blog_id

I have a pretty big MT install after all these years, but is there anything I can do to stop MT taking so long? The queries often end up Locking unrelated MySQL queries, which holds up my public-facing sites too. Thanks.

Reported on Movable Type 5

1 Reply

| Add a Reply

Add a Reply

If you need to share template code, replace all the "<" signs with "&lt;" or use this utility.

Forum Groups

1774 6167

Last Topic: Template modules by Zielun on Feb 16, 2012

86 302

Last Topic: website entries by masoud on Oct 26, 2011

1434 5088

Last Topic: Maintenance announcement by Nick on Feb 17, 2012

695 2912

Last Topic: Insert Image / File Fails by Russ Miller on Feb 10, 2012

84 291

Last Topic: How to have some other characters in entry basename automatically written by Afshin Haghighatnia on Dec 22, 2011

174 740

Last Topic: Captcha images rendering slowly by ScottM on Feb 12, 2012

190 568

Last Topic: Analytics Reporting by michael webster on Feb 5, 2012

48 210

Last Topic: An idea and also a request by Afshin Haghighatnia on Jun 29, 2011

64 246

Last Topic: jQuery in MT 5.1 still at 1.4 - why? by perlmonkey on May 25, 2011

code.sixapart.com

137 478

Last Topic: Getting a thumbnail with xpath by Peter on Mar 13, 2011

222 720

Last Topic: Custom Field for Asset Not Appearing by android on Feb 9, 2012