We have a news backend with few thousand articles, the workflow leads to slow loading times...

Discussion regarding Joomla! 4.x Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
Smike
Joomla! Apprentice
Joomla! Apprentice
Posts: 10
Joined: Wed Mar 06, 2019 2:45 pm

We have a news backend with few thousand articles, the workflow leads to slow loading times...

Post by Smike » Tue Jun 25, 2024 1:31 pm

Hi we have multiple news portals and our ppl are complaining that the backend is so slow (2s load times). I took a look at it and found that the INNER JOIN leads to a temporary table with 22k entries for our smallest portal.

With INNER JOIN 500ms, LEFT JOIN its 1.5ms. In bigger portals that up to 5s and makes it quite unusable. Not sure if the DB is missing an index, i tried to optimize, but for now ill change that behaviour


EXPLAIN SELECT `a`.`id`,`a`.`asset_id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`catid`,`a`.`state`,`a`.`access`,`a`.`created`,`a`.`created_by`,`a`.`created_by_alias`,`a`.`modified`,`a`.`ordering`,`a`.`featured`,`a`.`language`,`a`.`hits`,`a`.`publish_up`,`a`.`publish_down`,`a`.`introtext`,`a`.`fulltext`,`a`.`note`,`a`.`images`,`a`.`metakey`,`a`.`metadesc`,`a`.`metadata`,`a`.`version`,`fp`.`featured_up`,`fp`.`featured_down`,`l`.`title` AS `language_title`,`l`.`image` AS `language_image`,`uc`.`name` AS `editor`,`ag`.`title` AS `access_level`,`c`.`title` AS `category_title`,`c`.`created_user_id` AS `category_uid`,`c`.`level` AS `category_level`,`c`.`published` AS `category_published`,`parent`.`title` AS `parent_category_title`,`parent`.`id` AS `parent_category_id`,`parent`.`created_user_id` AS `parent_category_uid`,`parent`.`level` AS `parent_category_level`,`ua`.`name` AS `author_name`,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`workflow_id` AS `workflow_id`,`w`.`title` AS `workflow_title`,COALESCE(NULLIF(ROUND(`v`.`rating_sum` / `v`.`rating_count`, 0), 0), 0) AS `rating`,COALESCE(NULLIF(`v`.`rating_count`, 0), 0) AS `rating_count`
FROM hycwr_content AS `a`
LEFT JOIN `hycwr_languages` AS `l` ON `l`.`lang_code` = `a`.`language`
LEFT JOIN `hycwr_content_frontpage` AS `fp` ON `fp`.`content_id` = `a`.`id`
LEFT JOIN `hycwr_users` AS `uc` ON `uc`.`id` = `a`.`checked_out`
LEFT JOIN `hycwr_viewlevels` AS `ag` ON `ag`.`id` = `a`.`access`
LEFT JOIN `hycwr_categories` AS `c` ON `c`.`id` = `a`.`catid`
LEFT JOIN `hycwr_categories` AS `parent` ON `parent`.`id` = `c`.`parent_id`
LEFT JOIN `hycwr_users` AS `ua` ON `ua`.`id` = `a`.`created_by`
INNER JOIN `hycwr_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `hycwr_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
INNER JOIN `hycwr_workflows` AS `w` ON `w`.`id` = `ws`.`workflow_id`
LEFT JOIN `hycwr_content_rating` AS `v` ON `a`.`id` = `v`.`content_id`
WHERE `wa`.`extension` = 'com_content.article' AND `a`.`state` IN (1,0)
ORDER BY a.id desc LIMIT 20;

EXPLAIN SELECT `a`.`id`,`a`.`asset_id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`catid`,`a`.`state`,`a`.`access`,`a`.`created`,`a`.`created_by`,`a`.`created_by_alias`,`a`.`modified`,`a`.`ordering`,`a`.`featured`,`a`.`language`,`a`.`hits`,`a`.`publish_up`,`a`.`publish_down`,`a`.`introtext`,`a`.`fulltext`,`a`.`note`,`a`.`images`,`a`.`metakey`,`a`.`metadesc`,`a`.`metadata`,`a`.`version`,`fp`.`featured_up`,`fp`.`featured_down`,`l`.`title` AS `language_title`,`l`.`image` AS `language_image`,`uc`.`name` AS `editor`,`ag`.`title` AS `access_level`,`c`.`title` AS `category_title`,`c`.`created_user_id` AS `category_uid`,`c`.`level` AS `category_level`,`c`.`published` AS `category_published`,`parent`.`title` AS `parent_category_title`,`parent`.`id` AS `parent_category_id`,`parent`.`created_user_id` AS `parent_category_uid`,`parent`.`level` AS `parent_category_level`,`ua`.`name` AS `author_name`,COALESCE(NULLIF(ROUND(`v`.`rating_sum` / `v`.`rating_count`, 0), 0), 0) AS `rating`,COALESCE(NULLIF(`v`.`rating_count`, 0), 0) AS `rating_count`
FROM `hycwr_content` AS `a`
LEFT JOIN `hycwr_languages` AS `l` ON `l`.`lang_code` = `a`.`language`
LEFT JOIN `hycwr_content_frontpage` AS `fp` ON `fp`.`content_id` = `a`.`id`
LEFT JOIN `hycwr_users` AS `uc` ON `uc`.`id` = `a`.`checked_out`
LEFT JOIN `hycwr_viewlevels` AS `ag` ON `ag`.`id` = `a`.`access`
LEFT JOIN `hycwr_categories` AS `c` ON `c`.`id` = `a`.`catid`
LEFT JOIN `hycwr_categories` AS `parent` ON `parent`.`id` = `c`.`parent_id`
LEFT JOIN `hycwr_users` AS `ua` ON `ua`.`id` = `a`.`created_by`
LEFT JOIN `hycwr_content_rating` AS `v` ON `a`.`id` = `v`.`content_id`
WHERE (a.state = 0 OR a.state = 1)
ORDER BY a.id desc LIMIT 20;

EXPLAIN SELECT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.note,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title, c.created_user_id AS category_uid, c.level AS category_level,parent.title AS parent_category_title, parent.id AS parent_category_id,
parent.created_user_id AS parent_category_uid, parent.level AS parent_category_level,ua.name AS author_name,COALESCE(NULLIF(ROUND(v.rating_sum / v.rating_count, 0), 0), 0) AS rating,
COALESCE(NULLIF(v.rating_count, 0), 0) as rating_count

FROM hycwr_content AS a

LEFT JOIN `hycwr_languages` AS l
ON l.lang_code = a.language

LEFT JOIN hycwr_users AS uc
ON uc.id=a.checked_out

LEFT JOIN hycwr_viewlevels AS ag
ON ag.id = a.access

LEFT JOIN hycwr_categories AS c
ON c.id = a.catid

LEFT JOIN hycwr_categories AS parent
ON parent.id = c.parent_id

LEFT JOIN hycwr_users AS ua
ON ua.id = a.created_by

LEFT JOIN hycwr_content_rating AS v
ON a.id = v.content_id

WHERE (a.state = 0 OR a.state = 1)

ORDER BY a.id desc
LIMIT 20;

Smike
Joomla! Apprentice
Joomla! Apprentice
Posts: 10
Joined: Wed Mar 06, 2019 2:45 pm

Re: We have a news backend with few thousand articles, the workflow leads to slow loading times...

Post by Smike » Tue Jun 25, 2024 2:31 pm

Also in the live system the index primary needed to be enforced, or it did use also a slow query. im not sure why though, it had the same db.

->join('LEFT', $db->quoteName('#__workflow_associations', 'wa'), $db->quoteName('wa.item_id') . ' = ' . $db->quoteName('a.id'))

LEFT JOIN `hycwr_workflow_associations` AS `wa` FORCE INDEX (PRIMARY) ON `wa`.`item_id` = `a`.`id`


Post Reply

Return to “Performance - Joomla! 4.x”