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 100SELECT 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 DESCSELECT 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_idSELECT 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

Sorry, I don't think that first query is from MT front page, but the others are. I still haven't found what's causing the first one!