combined view forum view very slow due to...

Posted May 5, 20222 yr

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, 20222 yr by sound

princeton
1

June 23, 20222 yr

Author

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?