combined view forum view very slow due to...

[sound]

Our mariadb slow query logs pointed to poor results when guests were viewing the new 'combined view' topic listing  (it was fine when members were viewing)

4.8 seconds + for the query below

/*xxxx::IPS\Helpers\Table\_Content::getRows:489*/
 SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text
 
 FROM `ibf_forums_topics` AS `forums_topics`    FORCE INDEX (last_post)
 
 
 LEFT JOIN `ibf_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id 
 
 
 WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(1,2,128) ORDER BY forums_topics.last_post desc LIMIT 0,50;

 

this slow query appeared to be due to the effect the where clause ' forums_forums.can_view_others=1'  was having on the query, maybe index issues?

removing this clause via the getItemsWithPermissionWhere function

if ( !$member->member_id )
				{
					/// $where[] = array( 'forums_forums.can_view_others=1' );
				}

resulted in a 'normal' speed listing of 0.8 seconds for guests

maybe worth a dig ?

 

 

Edited May 5, 2022 by sound

[sound]

On 5/5/2022 at 6:56 PM, sound said:

Our mariadb slow query logs pointed to poor results when guests were viewing the new 'combined view' topic listing  (it was fine when members were viewing)

4.8 seconds + for the query below

/*xxxx::IPS\Helpers\Table\_Content::getRows:489*/
 SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text
 
 FROM `ibf_forums_topics` AS `forums_topics`    FORCE INDEX (last_post)
 
 
 LEFT JOIN `ibf_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = forums_topics.starter_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id 
 
 
 WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(1,2,128) ORDER BY forums_topics.last_post desc LIMIT 0,50;

 

this slow query appeared to be due to the effect the where clause ' forums_forums.can_view_others=1'  was having on the query, maybe index issues?

removing this clause via the getItemsWithPermissionWhere function

if ( !$member->member_id )
				{
					/// $where[] = array( 'forums_forums.can_view_others=1' );
				}

resulted in a 'normal' speed listing of 0.8 seconds for guests

maybe worth a dig ?

 

 

 

anyone else getting slow times  for the guest viewing of the new 'combined view' topic listing ?

 

my thinking is if the above hack  does save 3/4 seconds each query (in my case) then would a check if the site has any such forums using the "can't view other posts' before added it as a 'where clause' make sense?

 

[Martin A.]

Can add that we're seeing the same numbers. Takes 14+ seconds to load a combined view with 2 subforums. 

[Daniel F]

Thanks for reporting this, I have forwarded this to @Matt who's our DB Master:)

[Martin A.]

Any news on this? Our slow query log is FULL of this now.

[sound]

 

On 6/23/2022 at 7:44 PM, Daniel F said:

Thanks for reporting this, I have forwarded this to @Matt who's our DB Master:)

latest upgrade and for me this is still an issue

3-4 seconds without own suggested edit, normal speed with edit