Public Forums 71,145 posts

[Andrew Winkler Friend Andrew WinklerJoin date: September 2014Posts: 725Downloads:206Uploads:31 Thanks: 291 Thanked: 34 times in 2 posts]

#175708

I’ve recently upgraded my joomla 1.5 site to joomla 2.5, starting the site from scratch. So far the site (therebel.org) has only a few hundred content records, as opposed to the 15,000-20,000 records it used to have with the old site. The site is using three JA templates, JA social, JA nex and JA pyro. JA social is used as the default template for the blog, Jomsocial and Kunena parts of the site. JA nex is used for the news section, JA pyro for the education part of the site, which is still under construction. The day I installed JA pyro, I was contact by the hosting company, siteground.com, complaining about very slow SQL queries. They are very concerned about the stress those queries are putting on the shared server and are threatening to disable my site. The two queries in question are this:

Executed 17 min ago for 10 sec on Database –> rebelins_db.

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_publishedFROM j25_content AS aLEFT JOIN j25_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN j25_categories AS c ON c.id = a.catidLEFT JOIN j25_users AS ua ON ua.id = a.created_byLEFT JOIN j25_users AS uam ON uam.id = a.modified_byLEFT JOIN (SELECT contact.user_id, MAX(contact.id) AS id, contact.languageFROM j25_contact_details AS contactWHERE contact.published = 1GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_byLEFT JOIN j25_categories as parent ON parent.id = c.parent_idLEFT JOIN j25_content_rating AS v ON a.id = v.content_idLEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.idWHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-04-02 16:45:05') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-04-02 16:45:05')GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urlsORDER BY a.created DESC , a.created LIMIT 0, 20

Executed 12 min ago for 8 sec on Database –> rebelins_db.

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_publishedFROM j25_content AS aLEFT JOIN j25_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN j25_categories AS c ON c.id = a.catidLEFT JOIN j25_users AS ua ON ua.id = a.created_byLEFT JOIN j25_users AS uam ON uam.id = a.modified_byLEFT JOIN (SELECT contact.user_id, MAX(contact.id) AS id, contact.languageFROM j25_contact_details AS contactWHERE contact.published = 1GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_byLEFT JOIN j25_categories as parent ON parent.id = c.parent_idLEFT JOIN j25_content_rating AS v ON a.id = v.content_idLEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.idWHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-04-02 16:56:44') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-04-02 16:56:44')GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urlsORDER BY a.created DESC , a.created LIMIT 0, 20

While it appears that there seem to be generic performance problems in Joomla 2.5 with these queries, they seem to be aggravated by your templates, because the hosting company only started complaining about them after installing the third template, JA pyro.

[Sherlock Friend SherlockJoin date: September 2014Posts: 11453Downloads:0Uploads:88 Thanks: 221 Thanked: 2478 times in 2162 posts]

#447386

Hi andrewwinkler,

I don’t think this could be a problem with the JA pyro template as well as any other templates, as you know on almost every joomla site the template mainly took the mission of displaying data (html structure and contents), the responsibility of getting data belong to extensions so please check again your extension that is getting and showing article contents, try to disable them one by one to know which one holding the problem.

1 user says Thank You to Sherlock for this useful post

1. Andrew Winkler

[zomidaily Friend zomidailyJoin date: March 2013Posts: 132Downloads:0Uploads:4 Thanks: 7 Thanked: 9 times in 1 posts]

#494349

Hi andrewwinkler,

How is your issue? Have you sorted out?

I also hosted on SiteGround, and I was informed that the site is using too much server resources with lots of slow queries.

Would be really appreciate if you could share your solution.

My site is http://www.zomidaily.com and currently I’m using JA Mitius template.

Regards,
Joseph

<em>@andrewwinkler 311876 wrote:</em><blockquote>I’ve recently upgraded my joomla 1.5 site to joomla 2.5, starting the site from scratch. So far the site (therebel.org) has only a few hundred content records, as opposed to the 15,000-20,000 records it used to have with the old site. The site is using three JA templates, JA social, JA nex and JA pyro. JA social is used as the default template for the blog, Jomsocial and Kunena parts of the site. JA nex is used for the news section, JA pyro for the education part of the site, which is still under construction. The day I installed JA pyro, I was contact by the hosting company, siteground.com, complaining about very slow SQL queries. They are very concerned about the stress those queries are putting on the shared server and are threatening to disable my site. The two queries in question are this:

Executed 17 min ago for 10 sec on Database –> rebelins_db.

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_publishedFROM j25_content AS aLEFT JOIN j25_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN j25_categories AS c ON c.id = a.catidLEFT JOIN j25_users AS ua ON ua.id = a.created_byLEFT JOIN j25_users AS uam ON uam.id = a.modified_byLEFT JOIN (SELECT contact.user_id, MAX(contact.id) AS id, contact.languageFROM j25_contact_details AS contactWHERE contact.published = 1GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_byLEFT JOIN j25_categories as parent ON parent.id = c.parent_idLEFT JOIN j25_content_rating AS v ON a.id = v.content_idLEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.idWHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-04-02 16:45:05') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-04-02 16:45:05')GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urlsORDER BY a.created DESC , a.created LIMIT 0, 20

Executed 12 min ago for 8 sec on Database –> rebelins_db.

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_publishedFROM j25_content AS aLEFT JOIN j25_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN j25_categories AS c ON c.id = a.catidLEFT JOIN j25_users AS ua ON ua.id = a.created_byLEFT JOIN j25_users AS uam ON uam.id = a.modified_byLEFT JOIN (SELECT contact.user_id, MAX(contact.id) AS id, contact.languageFROM j25_contact_details AS contactWHERE contact.published = 1GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_byLEFT JOIN j25_categories as parent ON parent.id = c.parent_idLEFT JOIN j25_content_rating AS v ON a.id = v.content_idLEFT OUTER JOIN (SELECT cat.id as id FROM j25_categories AS cat JOIN j25_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.idWHERE a.access IN (1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-04-02 16:56:44') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-04-02 16:56:44')GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urlsORDER BY a.created DESC , a.created LIMIT 0, 20

While it appears that there seem to be generic performance problems in Joomla 2.5 with these queries, they seem to be aggravated by your templates, because the hosting company only started complaining about them after installing the third template, JA pyro.</blockquote>

[Ninja Lead Moderator Ninja LeadJoin date: November 2014Posts: 16064Downloads:310Uploads:2864 Thanks: 341 Thanked: 3854 times in 3563 posts]

#494710

Hi Joseph,

There are many ways to speed up your website:

+ Remove unnecessary White Space in HTML
+ Minify Javascript and CSS
+ Reduce requests by combining HTML, CSS, and Javascript
+ Optimize all your images – Most if not all should be at least below 10kb
– Crop the white space using Photoshop
– Use PNG8 Files or GIF files rather than Jpegs and don’t use transparency
– Make images the size you want rather than resizing them in the editor
+ Uninstall any components/modules/plugins that you don’t actually use
+ Use Gzip Compression to Reduce HTML Size
+ Enable Caching as much as possible (page caching, view caching, module caching)

Regards

[xumbrella Friend xumbrellaJoin date: June 2010Posts: 100Downloads:0Uploads:10 Thanks: 6 Thanked: 2 times in 1 posts]

#532371

Hi Andrew, did you ever solve this issue?

I have exactly the same issue using Joomlart template JA wall with my host complaining of exactly the same performance issue with that query. I did some research on the net and it seems the slow query is a result of large joomla db (mine has 50K+) articles, and this query checks every record in the DB to build the page structure. The problem file is supposedly /components/com_content/models/articles.php…

Problem is, no one seems to have posted an answer.

In my case the problem is very apparent because of the way JA Wall is designed it loads all category blog posts to the home page so the site has to query everything when you first visit the site. On category blog pages with small amount of articles the problem is less apparent. So, in conclusion it looks like it is a problem that is inherent in the core of joomla but made noticable by some templates, names JA wall and possible the one you are using.

I wonder if any Joomlart admins can add any comments to this issue, because right now this is killing my site, 11 secs to go through the DB and 1.5 to load the page assets!! Only other solution I saw suggested was to through more processing power at it and get a VPS cloud host, but this is an expensive option that should not really be the solution if the DB query is the cause.

thanks

[xumbrella Friend xumbrellaJoin date: June 2010Posts: 100Downloads:0Uploads:10 Thanks: 6 Thanked: 2 times in 1 posts]

#532373

In addition, I just found the below that suggests this problem is fixed since Joomla 3.1.2, however, since I am running 2.5.x and was told on this forum that I should not migrate I am stuck as to what to do,I do not want to “hack” core joomla code.

Do any admins know if this issue was fixed since 3.1.2 and whether I should migrate (is this difficult / problematic)?

This has been solved in latest GitHub version for Joomla 3.1.2. (using COUNT instead of loading all)

Patch by Matias (Thanks Matias) in FR:
# [#31278] Improve counting of results in queries. Thanks Matias Griese (Fix #1274):

https://github.com/joomla/joomla-cms/pull/1274

You can either apply patch, or get github version (not for live site, which also includes the SQL query profiler that I added with Peter), or wait for the upcoming Joomla 3.1.2 release.

[Ninja Lead Moderator Ninja LeadJoin date: November 2014Posts: 16064Downloads:310Uploads:2864 Thanks: 341 Thanked: 3854 times in 3563 posts]

#532393

@xumbrella, Thanks for sharing your feedback.

By the way, please give it a try with JA Wall for J3.2 here when you have time.

[Author]

Posts