Long MySQL Queries in Supergrid

[scott101]

I've had a few issues I'm trying to work out so I set up a long query log for 10 seconds or more, and noticed that the Supergrid seems to have long query times--several times over two days. Any idea why this would be the case?

# Time: 240719 11:23:50
# User@Host: site_forum[site_forum] @ localhost []
# Thread_id: 270920  Schema: database_forum_03  QC_hit: No
# Query_time: 11.151348  Lock_time: 0.000033  Rows_sent: 17  Rows_examined: 22996
# Rows_affected: 0  Bytes_sent: 262144
SET timestamp=1721413430;
/*database_forum_03::site_forum::IPS\Theme\class_cms_database_supergrid_frontpage::index:142*/ SELECT cms_custom_database_2.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ibf_cms_custom_database_2` AS `cms_custom_database_2`  LEFT JOIN `ibf_cms_database_categories` AS `cms_database_categories` ON cms_custom_database_2.category_id=cms_database_categories.category_id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('cms;records2;',cms_custom_database_2.primary_id_field))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = cms_custom_database_2.member_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = cms_custom_database_2.record_last_comment_by WHERE ( category_can_view_others=1 OR cms_custom_database_2.member_id IS NULL ) AND cms_custom_database_2.record_approved=1 AND cms_custom_database_2.record_future_date=0 AND cms_custom_database_2.category_id NOT IN (151) ORDER BY record_pinned DESC, record_publish_date desc LIMIT 0,17;

# Time: 240719 11:25:13
# User@Host: site_forum[site_forum] @ localhost []
# Thread_id: 271397  Schema: database_forum_03  QC_hit: No
# Query_time: 10.505374  Lock_time: 0.000055  Rows_sent: 17  Rows_examined: 22996
# Rows_affected: 0  Bytes_sent: 262144
SET timestamp=1721413513;
/*database_forum_03::site_forum::IPS\Theme\class_cms_database_supergrid_frontpage::index:142*/ SELECT cms_custom_database_2.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ibf_cms_custom_database_2` AS `cms_custom_database_2`  LEFT JOIN `ibf_cms_database_categories` AS `cms_database_categories` ON cms_custom_database_2.category_id=cms_database_categories.category_id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('cms;records2;',cms_custom_database_2.primary_id_field))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = cms_custom_database_2.member_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = cms_custom_database_2.record_last_comment_by WHERE ( category_can_view_others=1 OR cms_custom_database_2.member_id IS NULL ) AND cms_custom_database_2.record_approved=1 AND cms_custom_database_2.record_future_date=0 AND cms_custom_database_2.category_id NOT IN (151) ORDER BY record_pinned DESC, record_publish_date desc LIMIT 0,17;

# Time: 240719 11:25:20
# User@Host: site_forum[site_forum] @ localhost []
# Thread_id: 271420  Schema: database_forum_03  QC_hit: No
# Query_time: 13.176171  Lock_time: 0.000034  Rows_sent: 17  Rows_examined: 22996
# Rows_affected: 0  Bytes_sent: 262144
SET timestamp=1721413520;
/*database_forum_03::site_forum::IPS\Theme\class_cms_database_supergrid_frontpage::index:142*/ SELECT cms_custom_database_2.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ibf_cms_custom_database_2` AS `cms_custom_database_2`  LEFT JOIN `ibf_cms_database_categories` AS `cms_database_categories` ON cms_custom_database_2.category_id=cms_database_categories.category_id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('cms;records2;',cms_custom_database_2.primary_id_field))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = cms_custom_database_2.member_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = cms_custom_database_2.record_last_comment_by WHERE ( category_can_view_others=1 OR cms_custom_database_2.member_id IS NULL ) AND cms_custom_database_2.record_approved=1 AND cms_custom_database_2.record_future_date=0 AND cms_custom_database_2.category_id NOT IN (151) ORDER BY record_pinned DESC, record_publish_date desc LIMIT 0,17;

# Time: 240719 11:25:39
# User@Host: site_forum[site_forum] @ localhost []
# Thread_id: 271511  Schema: database_forum_03  QC_hit: No
# Query_time: 11.343138  Lock_time: 0.000037  Rows_sent: 17  Rows_examined: 22996
# Rows_affected: 0  Bytes_sent: 262144
SET timestamp=1721413539;
/*database_forum_03::site_forum::IPS\Theme\class_cms_database_supergrid_frontpage::index:142*/ SELECT cms_custom_database_2.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ibf_cms_custom_database_2` AS `cms_custom_database_2`  LEFT JOIN `ibf_cms_database_categories` AS `cms_database_categories` ON cms_custom_database_2.category_id=cms_database_categories.category_id  LEFT JOIN `ibf_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('cms;records2;',cms_custom_database_2.primary_id_field))  LEFT JOIN `ibf_core_members` AS `author` ON author.member_id = cms_custom_database_2.member_id  LEFT JOIN `ibf_core_members` AS `last_commenter` ON last_commenter.member_id = cms_custom_database_2.record_last_comment_by WHERE ( category_can_view_others=1 OR cms_custom_database_2.member_id IS NULL ) AND cms_custom_database_2.record_approved=1 AND cms_custom_database_2.record_future_date=0 AND cms_custom_database_2.category_id NOT IN (151) ORDER BY record_pinned DESC, record_publish_date desc LIMIT 0,17;

 

[scott101]

PS - I am running version 3.2.0 on the latest IPB version.

[admin]

SuperGrid is just a template set. It doesn’t contain any custom queries. The queries all come from the core and the data is then displayed through the SuperGrid template structure. That’s it. So I don’t think your issues are actually caused by SuperGrid.

[scott101]

Thanks for the info, I also created a thread on this on the IPB forum here, as this and another long query seem to be related to an issue I'm having with the sitemapgenerator task running properly:

https://invisioncommunity.com/forums/topic/476099-task-cronjob-suddenly-triggering-excessive-resource-usage-after-4714-upgrade/page/2/#comment-2974981