LaCollision Posted March 24, 2022 Posted March 24, 2022 Hi, I've noticed that when IPS.Pages (cms) is installed, and that the Service Worker makes a call to app=core&module=system&controller=serviceworker, then extra DB queries are always run, on all pages: SELECT cms_pages.*, core_permission_index.perm_id, core_permission_index.perm_view, core_permission_index.perm_2, core_permission_index.perm_3, core_permission_index.perm_4, core_permission_index.perm_5, core_permission_index.perm_6, core_permission_index.perm_7 FROM `cms_pages` LEFT JOIN `core_permission_index` ON core_permission_index.app='cms' AND core_permission_index.perm_type='pages' AND core_permission_index.perm_type_id=cms_pages.page_id WHERE cms_pages.page_full_path='app=core' SELECT * FROM `cms_folders` WHERE `folder_path`='app=core' SELECT store_current_id FROM `cms_url_store` WHERE store_type='page' and store_path='app=core' SELECT word_key, word_default, word_custom FROM `core_sys_lang_words` WHERE lang_id=3 AND word_key IN('__indefart_content_record_comments_title','__indefart_content_record_reviews_title','content_db_lang_ia_1','__defart_content_record_comments_title','__defart_content_record_reviews_title','content_db_lang_sl_1','content_db_lang_su_1','content_record_comment_title','content_record_review_title','content_record_comments_title','content_record_comments_title_lc','content_record_reviews_title','content_record_reviews_title_lc','content_db_lang_pu_1','content_db_lang_pl_1','content_field_1','content_field_2','content_field_3','default_notification_title','default_notification_body') and word_js=0 This extra queries seem to come from the IPS.Pages hooks, that add queries on the \IPS\Http\Url\Friendly, \IPS\Lang, etc. classes. These extra queries are added even when IPS.Pages is not defined as the root page of the community. => Isn't it possible to avoid such extra load on all pages? Thank you!
Jim M Posted March 24, 2022 Posted March 24, 2022 Are you using Pages related blocks on these pages where you're seeing the service worker?
LaCollision Posted March 24, 2022 Author Posted March 24, 2022 Hi Jim, No, not at all! The problem happens even on blank templates outside the IPS.Pages app. When the app=core&module=system&controller=serviceworker page is called, several methods like \IPS\Member::loggedIn()->language() and parseOutputForDisplay() are called. You can see them in \IPS\core\modules\front\system\serviceworker.php, at line 75: \IPS\Member::loggedIn()->language()->parseOutputForDisplay( $output ); And it turns out that IPS.Pages overrides in its hooks many underlying methods of the methods above, like: public static function createFriendlyUrlFromComponents( $components, $potentialFurl ) { /* If the normal URL handling has it, or this is the root page, use normal handling, unless Pages is the default app, in which case we'll fallback to it */ if ( $return = parent::createFriendlyUrlFromComponents( $components, $potentialFurl ) or !$potentialFurl ) { if ( !\IPS\Application::load('cms')->default OR $potentialFurl ) { return $return; } } /* Try to find a page */ try { list( $pagePath, $pageNumber ) = \IPS\cms\Pages\Page::getStrippedPagePath( $potentialFurl ); try { $page = \IPS\cms\Pages\Page::loadFromPath( $pagePath ); } catch( \Exception $e ) { /* Try from furl */ try { $page = \IPS\cms\Pages\Page::load( \IPS\Db::i()->select( 'store_current_id', 'cms_url_store', array( 'store_type=? and store_path=?', 'page', $pagePath ) )->first() ); } catch( \UnderflowException $e ) { throw new \OutOfRangeException; } } return static::createFromComponents( $components[ static::COMPONENT_HOST ], $components[ static::COMPONENT_SCHEME ], $components[ static::COMPONENT_PATH ], $components[ static::COMPONENT_QUERY ], $components[ static::COMPONENT_PORT ], $components[ static::COMPONENT_USERNAME ], $components[ static::COMPONENT_PASSWORD ], $components[ static::COMPONENT_FRAGMENT ] ) ->setFriendlyUrlData( 'content_page_path', array( $potentialFurl ), array( 'path' => $potentialFurl ), $potentialFurl ); } … } and : public function languageInit() { /* Language, is it? */ parent::languageInit(); /* Don't do this during setup */ if ( \IPS\Dispatcher::hasInstance() AND \IPS\Dispatcher::i()->controllerLocation == 'setup' ) { return; } /* Ensure applications set up correctly before task is executed. Pages, for example, needs to set up spl autoloaders first */ \IPS\Application::applications(); /* Add in the database specific language bits and bobs */ foreach( \IPS\cms\Databases::getStore() as $database ) { $this->words['__indefart_content_record_comments_title_' . $database['database_id'] ] = $this->addToStack( '__indefart_content_record_comments_title' ); $this->words['__indefart_content_record_reviews_title_' . $database['database_id'] ] = $this->addToStack( '__indefart_content_record_reviews_title' ); $this->words['__indefart_content_db_lang_su_' . $database['database_id'] ] = $this->addToStack( 'content_db_lang_ia_' . $database['database_id'] ); $this->words['__defart_content_record_comments_title_' . $database['database_id'] ] = $this->addToStack( '__defart_content_record_comments_title' ); $this->words['__defart_content_record_reviews_title_' . $database['database_id'] ] = $this->addToStack( '__defart_content_record_reviews_title' ); $this->words['__defart_content_db_lang_su_' . $database['database_id'] ] = $this->addToStack( 'content_db_lang_sl_' . $database['database_id'] ); $this->words['content_record_comments_title_' . $database['database_id'] ] = $this->addToStack( 'content_record_comment_title', FALSE, array( 'sprintf' => array( $this->recordWord( 1, TRUE, $database['database_id'] ) ) ) ); $this->words['content_record_reviews_title_' . $database['database_id'] ] = $this->addToStack( 'content_record_review_title', FALSE, array( 'sprintf' => array( $this->recordWord( 1, TRUE, $database['database_id'] ) ) ) ); $this->words['content_record_comments_title_' . $database['database_id'] . '_pl' ] = $this->addToStack( 'content_record_comments_title', FALSE, array( 'sprintf' => array( $this->recordWord( 1, TRUE, $database['database_id'] ) ) ) ); $this->words['content_record_comments_title_' . $database['database_id'] . '_pl_lc' ] = $this->addToStack( 'content_record_comments_title_lc', FALSE, array( 'sprintf' => array( $this->recordWord( 1, FALSE, $database['database_id'] ) ) ) ); $this->words['content_record_comments_title_' . $database['database_id'] . '_lc' ] = $this->addToStack( 'content_record_comments_title_lc', FALSE, array( 'sprintf' => array( $this->recordWord( 1, FALSE, $database['database_id'] ) ) ) ); $this->words['content_record_reviews_title_' . $database['database_id'] . '_pl' ] = $this->addToStack( 'content_record_reviews_title', FALSE, array( 'sprintf' => array( $this->recordWord( 1, TRUE, $database['database_id'] ) ) ) ); $this->words['content_record_reviews_title_' . $database['database_id'] . '_pl_lc' ] = $this->addToStack( 'content_record_reviews_title_lc', FALSE, array( 'sprintf' => array( $this->recordWord( 1, FALSE, $database['database_id'] ) ) ) ); $this->words['content_record_reviews_title_' . $database['database_id'] . '_lc' ] = $this->addToStack( 'content_record_reviews_title_lc', FALSE, array( 'sprintf' => array( $this->recordWord( 1, FALSE, $database['database_id'] ) ) ) ); $this->words['content_db_lang_su_' . $database['database_id'] . '_pl' ] = $this->addToStack( 'content_db_lang_pu_' . $database['database_id'] ); $this->words['content_db_lang_su_' . $database['database_id'] . '_pl_lc' ] = $this->addToStack( 'content_db_lang_pl_' . $database['database_id'] ); $this->words['content_db_lang_sl_' . $database['database_id'] . '_pl_lc' ] = $this->addToStack( 'content_db_lang_pl_' . $database['database_id'] ); $fieldsClass = '\IPS\cms\Fields' . $database['database_id']; $customFields = $fieldsClass::databaseFieldIds(); foreach ( $customFields AS $id ) { $this->words['sort_field_' . $id] = $this->addToStack( 'content_field_' . $id ); } } } You can see that these overridden methods are responsible for at least some of the extra queries mentioned in my 1st post. Thank you for your help!
Jim M Posted March 24, 2022 Posted March 24, 2022 Please be advised I have tagged this to a developer to review. Someone will be with you as soon as they can. LaCollision 1
LaCollision Posted March 24, 2022 Author Posted March 24, 2022 Hi again, In my message above, the hook \IPS\cms\hooks\Lang::languageInit() calls \IPS\cms\Databases::getStore(), which is another culprit for extra DB queries: public static function getStore() { if ( ! isset( \IPS\Data\Store::i()->cms_databases ) ) { \IPS\Data\Store::i()->cms_databases = iterator_to_array( \IPS\Db::i()->select( static::$databaseTable . '.*, core_permission_index.perm_id, core_permission_index.perm_view, core_permission_index.perm_2, core_permission_index.perm_3, core_permission_index.perm_4, core_permission_index.perm_5, core_permission_index.perm_6, core_permission_index.perm_7', static::$databaseTable )->join( 'core_permission_index', array( "core_permission_index.app=? AND core_permission_index.perm_type=? AND core_permission_index.perm_type_id=" . static::$databaseTable . "." . static::$databasePrefix . static::$databaseColumnId, static::$permApp, static::$permType ) ) ->setKeyField('database_id') ); } return \IPS\Data\Store::i()->cms_databases; } Thank you! 😅
Ryan Ashbrook Posted March 24, 2022 Posted March 24, 2022 For the language query - that query always runs, because that is the main query that loads all of the words that will be used on a particular page. The only time that query would likely ever not be ran, is if there are no language strings on the page, which will be rare. That query isn't actually run by Pages, and is actually ran by the core itself. The other queries are necessary because Pages has it's own unique routing system, so those queries are necessary to determine if you are on a Pages page if the URL of the page you are viewing does not match up to an existing friendly URL from one of the other apps.
Recommended Posts