john-doe wrote: ↑Fri Jul 12, 2019 3:21 pm
Greetings. What is the Size in MB of the SQL file?
Normally when i have to deal with Heavy Databases i do it using akeeba backup but i'm not sure of the magnitude of the size of DB on your site.
I had once a case where DB was 2.1GB of size but the Largest portion of it was Spam Users in the registration, something can be easily done in a php admin operation.
I manage a site which has around 3000 articles and caching it helps a bit.
I don't see anything wrong in the settings so far.
The size of database is almost 5G
The largest tables are
bwn_ucm_history | 3170.98 Mb
bwn_content | 1409.66 Mb
bwn_ucm_content | 348.28 Mb
My problem is not on the front end but at the backend. Listing articles takes too long. Especially when two or more authors submitting simultaneously.
By debuging the system I see that 71 queries! are running at the articles listing pages and 2 of them are taking too long. Below the first one takes 7sec and the second 2 sec
This one takes 7 sec
Code: Select all
SELECT DISTINCT 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.introtext,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`condition` AS `stage_condition`,`ws`.`workflow_id` AS `workflow_id`,COUNT(asso2.id)>1 as association
FROM bwn_content AS a
LEFT JOIN `bwn_languages` AS l ON l.lang_code = a.language
LEFT JOIN bwn_users AS uc ON uc.id=a.checked_out
LEFT JOIN bwn_viewlevels AS ag ON ag.id = a.access
LEFT JOIN bwn_categories AS c ON c.id = a.catid
LEFT JOIN bwn_users AS ua ON ua.id = a.created_by
INNER JOIN `bwn_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `bwn_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
LEFT JOIN bwn_associations AS asso ON asso.id = a.id AND asso.context='com_content.item'
LEFT JOIN bwn_associations AS asso2 ON asso2.key = asso.key
WHERE `ws`.`condition` IN (1, 0) AND `wa`.`extension`='com_content'
GROUP BY `a`.`id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`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`.`catid`,`l`.`title`,`l`.`image`,`uc`.`name`,`ag`.`title`,`c`.`title`,`ua`.`name`,`ws`.`title`,`ws`.`workflow_id`,`ws`.`condition`,`wa`.`stage_id`
ORDER BY a.id DESC
This one takes 2.5 sec
Code: Select all
SELECT DISTINCT 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.introtext,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`condition` AS `stage_condition`,`ws`.`workflow_id` AS `workflow_id`,COUNT(asso2.id)>1 as association
FROM bwn_content AS a
LEFT JOIN `bwn_languages` AS l ON l.lang_code = a.language
LEFT JOIN bwn_users AS uc ON uc.id=a.checked_out
LEFT JOIN bwn_viewlevels AS ag ON ag.id = a.access
LEFT JOIN bwn_categories AS c ON c.id = a.catid
LEFT JOIN bwn_users AS ua ON ua.id = a.created_by
INNER JOIN `bwn_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `bwn_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
LEFT JOIN bwn_associations AS asso ON asso.id = a.id AND asso.context='com_content.item'
LEFT JOIN bwn_associations AS asso2 ON asso2.key = asso.key
WHERE `ws`.`condition` IN (1, 0) AND `wa`.`extension`='com_content'
GROUP BY `a`.`id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`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`.`catid`,`l`.`title`,`l`.`image`,`uc`.`name`,`ag`.`title`,`c`.`title`,`ua`.`name`,`ws`.`title`,`ws`.`workflow_id`,`ws`.`condition`,`wa`.`stage_id`
ORDER BY a.id DESC LIMIT 20