Jump to content

combined view forum view very slow due to...


Recommended Posts

Posted (edited)

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 by sound
Link to comment
Share on other sites

  • 1 month later...
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?

 

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...