Help with slow query

[Nacho-ED]

Hi, I'm having trouble with a query that forum, it takes about 15 seconds to run and leave the forum blocked until the end, can you help me fix it?

Thank you.

The example query is:

/*IPS\Patterns\_ActiveRecordIterator::count:251*/ SELECT SQL_CALC_FOUND_ROWS forums_posts.*, forums_topics.* FROM `forums_posts` STRAIGHT_JOIN `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.approved=1 STRAIGHT_JOIN `core_permission_index` ON core_permission_index.app=? AND core_permission_index.perm_type=? AND core_permission_index.perm_type_id=forums_topics.forum_id AND (( ( FIND_IN_SET(2,perm_2) ) ) OR perm_2=? ) LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id AND ( forums_forums.password IS NULL OR ( ( FIND_IN_SET(2,forums_forums.password_override) ) ) ) AND forums_forums.min_posts_view<=? WHERE ( forums_forums.can_view_others=1 OR forums_topics.starter_id IS NULL ) AND queued=? ORDER BY post_date desc LIMIT 1497400,25 

 

[Nacho-ED]

Hello, I do not get to find out what process you run this query, it occurs every few minutes, and forum blocked until it ends.

The database occupies 2GB, 1.641K posts and 178K topics. 40% of messages are archived in another database.

A couple of months ago worked all right and enduring well without having to archive any messages, with a total of 3.5GB database.

This lag in this query began to occur suddenly but had made no changes.

The forum is updated to the latest version.

Can anyone please help me?

Thank you very much.

# Query_time: 85.103970  Lock_time: 0.000015 Rows_sent: 25  Rows_examined: 6565988
SET timestamp=1442903968;
/*IPS\Patterns\_ActiveRecordIterator::count:251*/ SELECT SQL_CALC_FOUND_ROWS forums_posts.*, forums_topics.* FROM `forums_posts` STRAIGHT_JOIN `forums_topics` ON forums_posts.topic_id=forums_topics.tid AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.approved=1 STRAIGHT_JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_topics.forum_id AND (( ( FIND_IN_SET(2,perm_2) ) ) OR perm_2='*' ) LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id AND ( forums_forums.password IS NULL OR ( ( FIND_IN_SET(2,forums_forums.password_override) ) ) ) AND forums_forums.min_posts_view<=0 WHERE ( forums_forums.can_view_others=1 OR forums_topics.starter_id IS NULL ) AND queued=0 ORDER BY post_date desc LIMIT 1631725,25;

[VizionDev]

If you're joining cross-database, it's gotta get pretty slow with that size of a database.

I believe eliminating your archive would resolve your issue, atleast export it out then check for a change, if no change place them back and continue searching for a solution

[Nacho-ED]

These queries started slow before archiving posts, previously had no archiving topic, to solve a little problem have been increasingly archiving posts and queries time has been reduced but still excessive.

Thanks!!

[VizionDev]

7 minutes ago, Nacho-ED said:

These queries started slow before archiving posts, previously had no archiving topic, to solve a little problem have been increasingly archiving posts and queries time has been reduced but still excessive.

Thanks!!

My pleasure

I would probably recommend opening a support ticket for this also.

[Nacho-ED]

Thanks, I do it

 

[Nacho-ED]

Hello support staff told me the solution, If this can help another user with the same problem:

I believe this may be coming from the Activity Stream page. Specifically, when a bot attempts to load a page really far back in the stream. If you go to your Admin CP > System > Applications, click on the bar for the System application to expand the list of modules. Click the lock icon next to "Activity" and uncheck the box for Guests here, and then save.