Re: Slow queries on large website
Posted: Wed Feb 08, 2012 6:11 pm
Hi Gary,
any updates yet?
any updates yet?
Joomla! Community, help and support.
https://forum.joomla.org/
Did you run the patch? If so, what is the outcome?BBC2009 wrote:Does this patch require new install ?
http://joomlacode.org/gf/project/joomla ... m_id=27774
I just opened last 2.5.1 Joomla ZIP (from Joomla website) and i see that original joomla.sql in "installation" folder has 1676 lines. This one from patch has 6431 lines.
Code: Select all
SELECT b.rules
FROM j17_assets AS a
LEFT JOIN j17_assets AS b ON b.lft <= a.lft
AND b.rgt >= a.rgt
WHERE (
a.id =778
OR a.parent_id =0
)
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lft
LIMIT 0 , 30;
I migrated from 1.5 to 1.7 and then to 2.5garyjaybrooks wrote:I need to take inventory from everyone.
Who has a NEW install of Joomla 2.5 that is slow?
Who imported data from 1.5?
What tool did you use if you imported your data from 1.5?
Gary Brooks
I have 2.5 updated from 1.7. Same problem with both.garyjaybrooks wrote:I need to take inventory from everyone.
Who has a NEW install of Joomla 2.5 that is slow?
Who imported data from 1.5?
What tool did you use if you imported your data from 1.5?
Gary Brooks
No, i don´t know how to use it without new install. And waiting for it to show in Admin - Update.Rik Brown wrote:Did you run the patch? If so, what is the outcome?BBC2009 wrote:Does this patch require new install ?
http://joomlacode.org/gf/project/joomla ... m_id=27774
I just opened last 2.5.1 Joomla ZIP (from Joomla website) and i see that original joomla.sql in "installation" folder has 1676 lines. This one from patch has 6431 lines.
Very anxious for a solution.
Thanks. -- Rik
This is what i get when i run this PHP file:TBone wrote:The query slowing things down in my case was this one:
Which is used on lot's of pages in front and backend.Code: Select all
SELECT b.rules FROM j17_assets AS a LEFT JOIN j17_assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgt WHERE ( a.id =778 OR a.parent_id =0 ) GROUP BY b.id, b.rules, b.lft ORDER BY b.lft LIMIT 0 , 30;
The delay was caused as I mentioned earlier by wrong parentid values in #_assets table.
After searching I found a piece of code on http://joomlacode.org/gf/project/joomla ... m_id=25699
The piece of code is in the attachment.
Just execute the php script.
Make sure you make a backup of your #_assets table first!
You could also try the second piece of code in the post that fixes the lft and rgt records in this same table.
It worked for me.
Good luck.
Code: Select all
827-8 328-3 729-7 1930-19 2531-25 834-8 835-8 840-8 841-8 842-8 843-8 844-8 45-51 850-8 851-8 852-8 853-8 854-8 55-51 56-51 57-51 58-51 59-51 360-3 864-8 383-3 JLIB_DATABASE_ERROR_REBUILD_FAILED
Gary:garyjaybrooks wrote:I need to take inventory from everyone.
Who has a NEW install of Joomla 2.5 that is slow?
Who imported data from 1.5?
What tool did you use if you imported your data from 1.5?
Gary Brooks
EDIT: CODE PREVIOUSLY HERE WAS INCOMPLETE. SEE THE COMPLETE CODE HERE.flashgordonweb wrote:Hi Rik, can you tell us what query is the one really slowing down your site? I have a site with 13,000 articles that is currently sitting in 1.7.5 because of this issue. Have you looked at the query associated with ContentModelArticles::getListQuery() ? Or is your query problem something else? Thanks.
I get this moving around the site in both the front end and back end. Its hard to tell exactly which pages fire it since it seems to show up all over the place. I know that I can kill it (via a mysql utility) and it doesn't seem to make any difference on the front end.TBone wrote:On what type of page is this query fired?
Believe me i am thinking about Drupal too. A lot of work to make same website in Drupal, but what to do.tappy52dog wrote:So it seems like after Joomla 1.5, site speed has only gotten worse with 1.7 and even worse yet with 2.5? Joomla was already slow with 1.5, I can't imagine 2.5 being even a slower CMS. I hope they fix this as I really don't want to push clients back to Drupal and Wordpress if not needed.
Code: Select all
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 = N THEN a.created ELSE a$
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = N
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'S' AND parent.published != N $
WHERE a.access IN (N) AND CASE WHEN badcats.id is null THEN a.state ELSE N END = N AND (a.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
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.na$
ORDER BY c.lft, a.ordering, a.created LIMIT N, N
Code: Select all
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 = N THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = N 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 N 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 > 'S' 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, N) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE N END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = N
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'S' AND parent.published != N GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (N) AND CASE WHEN badcats.id is null THEN a.state ELSE N END = N AND (a.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
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.urls
ORDER BY c.lft, a.ordering, a.created LIMIT N, N
Nothing helped me.tappy52dog wrote:Has any cache extensions or settings helped you guys out at all?
RedEye:RedEye wrote:You not see all the N`s Rik? Also no need to repeat this query here ten times, because all suffer on the same queries, pls refer to page one of this thread, queries are there.
edit: Rik like I wrote in your other thread, if you not need latest/most read article module try to disable because the query you posting is from this.
Or you can try the STRAIGHT_JOIN I mentioned, it`s worth to give it try if it helps or not.
Code: Select all
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 > 'S' 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_published
FROM j17_content AS a
LEFT JOIN j17_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN j17_categories AS c ON c.id = a.catid
LEFT JOIN j17_users AS ua ON ua.id = a.created_by
LEFT JOIN j17_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM j17_contact_details AS contact
WHERE contact.published = 0
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN j17_categories as parent ON parent.id = c.parent_id
LEFT JOIN j17_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM j17_categories AS cat JOIN j17_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'S' AND parent.published != 0 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
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.urls
ORDER BY c.lft, a.ordering, a.created LIMIT 0, 1