Slow queries on large website

Discussion regarding Joomla! 2.5 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.
cuteschweinchen
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Fri Sep 09, 2011 1:10 pm

Re: Slow queries on large website

Post by cuteschweinchen » Wed Feb 08, 2012 6:11 pm

Hi Gary,

any updates yet?

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Thu Feb 09, 2012 10:34 am

Hi guys,
We also had an issue with performance after upgrading with Jupgrade. Problem was the #_assets table. All parentid's were set to "0". Please check your assets table. If you see lots of parentid's with 0 value this could be the problem.

To solve this issue you have to run a piece of code to set the parentid's to it's right value.

pawelp
Joomla! Apprentice
Joomla! Apprentice
Posts: 5
Joined: Wed Feb 01, 2012 6:32 am
Location: Poland
Contact:

Re: Slow queries on large website

Post by pawelp » Thu Feb 09, 2012 10:10 pm

TBone,

Can you share this code? Is it something very specific to your site? What is the correct value for parent id?

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Fri Feb 10, 2012 5:20 am

Most of my parent ids are not 0 and are different, and I am still facing the issue.
It's because of slow queries, as told to me by my webhost.
Last edited by puneetjain87 on Fri Feb 10, 2012 12:00 pm, edited 1 time in total.

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Fri Feb 10, 2012 6:21 am

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.
Did you run the patch? If so, what is the outcome?

Very anxious for a solution.

Thanks. -- Rik

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Fri Feb 10, 2012 6:36 am

Gary, have you come up with any solution?

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Fri Feb 10, 2012 8:01 am

The query slowing things down in my case was this one:

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;
Which is used on lot's of pages in front and backend.

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.
You do not have the required permissions to view the files attached to this post.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Feb 10, 2012 5:49 pm

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
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Sat Feb 11, 2012 5:20 am

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
I migrated from 1.5 to 1.7 and then to 2.5

I did a fresh install of 1.7 and just imported my articles and users from 1.5 to 1.7 (I did not use jupgrade)

upgrading to 2.5 was pretty simple, I just hit the update button and it happened.

Let me know if you need any other details.


My webshost says that query "copy to tmp table" creates lots of load on server and they need to shut down sql services because of that.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Sat Feb 11, 2012 9:11 pm

@Puneetjain87,

What tool did you use to move your articles from 1.5 to 1.7 and how many articles do you have?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Sun Feb 12, 2012 2:38 am

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
I have 2.5 updated from 1.7. Same problem with both.

- Website is build mostly with K2. And i am not a begginer with K2, regarding perfomance and K2 settings.
- Some cca 100 Items, not so heavy website.
- Debug shows some 90-100 queries in Homepage and some 40-60 queries Item view.

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Sun Feb 12, 2012 2:39 am

Rik Brown wrote:
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.
Did you run the patch? If so, what is the outcome?

Very anxious for a solution.

Thanks. -- Rik
No, i don´t know how to use it without new install. And waiting for it to show in Admin - Update.

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Sun Feb 12, 2012 7:12 am

@garyjaybrooks

I used J2XML http://extensions.joomla.org/extensions ... I7fQ%3D%3D to export my files from joomla 1.5

and used

J2XML Importer http://extensions.joomla.org/extensions ... I7fQ%3D%3D to import into Joomla 1.7

The number of articles was above 2000

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Sun Feb 12, 2012 12:21 pm

TBone wrote:The query slowing things down in my case was this one:

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;
Which is used on lot's of pages in front and backend.

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.
This is what i get when i run this PHP file:

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

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Sun Feb 12, 2012 2:25 pm

Anyway, i don´´t think it will work in my case. I don´t use Joomla native Sections/Categories and Joomla Articles at any of my websites.

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Sun Feb 12, 2012 4:28 pm

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
Gary:

I found Joomla 2.5 an improvement somewhat over 1.7.3. However, we have queries that can run a minute (when the query is triggered multiple times by different users) on a quite fast server with SSD drives and 12 GB of RAM. We do have a lot of articles and the JOIN queries produce huge tmp tables that can max out our ramdisk. I really don't understand the usage of those queries since the information is not displayed to anyone. I wish I could just turn it off.

I'd be happy to forward my mysql slow-query-log. There is one specific query which is really bogging down our site the most and it appears to be from the Joomla core. If the query doesn't run, the site delivers our pages at a very decent clip.

Thanks. -- Rik

PS: This is such a problem that I've been looking into using a mysql "kill" utility like mt-kill (now pt-kill) to kill any query running more than "x" seconds.

flashgordonweb
Joomla! Intern
Joomla! Intern
Posts: 74
Joined: Sat Mar 29, 2008 3:52 pm

Re: Slow queries on large website

Post by flashgordonweb » Mon Feb 13, 2012 12:24 am

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.

tappy52dog
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 115
Joined: Sat Dec 17, 2011 7:45 pm

Re: Slow queries on large website

Post by tappy52dog » Mon Feb 13, 2012 12:55 am

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.

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Mon Feb 13, 2012 6:55 am

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.
EDIT: CODE PREVIOUSLY HERE WAS INCOMPLETE. SEE THE COMPLETE CODE HERE.
Last edited by Rik Brown on Tue Feb 14, 2012 10:23 pm, edited 2 times in total.

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Mon Feb 13, 2012 8:04 am

On what type of page is this query fired?

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Mon Feb 13, 2012 1:37 pm

TBone wrote:On what type of page is this query fired?
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.

But the query also definitely triggers when I log into the backend and if I kill the query then, I get a 500 server error. So I let it run when I log into the backend. But I'm thinking of using mt-kill (now pt-kill) to kill the query on the front end unless it can get "fixed."

-- Rik

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Mon Feb 13, 2012 4:10 pm

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.
Believe me i am thinking about Drupal too. A lot of work to make same website in Drupal, but what to do.
Other solution is to convert to Joomla 1.5, some way or other.

No matter what i do pageload is relatively slow with only me as visitor. (or Live server with few visitors at the same time)
What would it look like when number of visitors increase significantly.

Joomla 1.5 with expire headers is space rocket. Joomla 1.7-2.5 with expire headers, almost as there are no expire headers set.

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Tue Feb 14, 2012 8:45 am

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
I tried to run this query on our 2.5.1 installation since I haven't got a 1.7 available right now. But it gives me several syntax errors. Could it be some things are missing. For example an 'END' after the 'ELSE a$'.

RedEye
Joomla! Ace
Joomla! Ace
Posts: 1460
Joined: Sat Jan 21, 2006 8:42 pm

Re: Slow queries on large website

Post by RedEye » Tue Feb 14, 2012 2:01 pm

Has someone tested if a STRAIGHT_JOIN helps with those queries?

tappy52dog
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 115
Joined: Sat Dec 17, 2011 7:45 pm

Re: Slow queries on large website

Post by tappy52dog » Tue Feb 14, 2012 4:27 pm

Has any cache extensions or settings helped you guys out at all?

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Tue Feb 14, 2012 10:18 pm

TBone:

I'm sorry. Wordwrap wasn't on when I cut that that query from my ssh client. Here is the entire code:

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
I'm so sorry for the confusion caused. -- Rik

RedEye
Joomla! Ace
Joomla! Ace
Posts: 1460
Joined: Sat Jan 21, 2006 8:42 pm

Re: Slow queries on large website

Post by RedEye » Wed Feb 15, 2012 4:24 am

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.

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Wed Feb 15, 2012 7:39 am

tappy52dog wrote:Has any cache extensions or settings helped you guys out at all?
Nothing helped me.

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Wed Feb 15, 2012 7:54 am

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.
RedEye:

"Most Read" module has been disabled some days ago, but I'm still getting this query executed. Something else must be triggering it.

-- Rik

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Wed Feb 15, 2012 8:04 am

Ah ok.
I tried running the query again in phpmyadmin
I modified all the N's

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
The query takes 0,27 seconds on NOT one of the most fast servers.
The database holds 1100 articles.

How many records does your jos_categories table contain? The most suspected part for slowing things down seems to be the LEFT OUTER JOIN on jos_categories in this query. If parent en rgt and lft where migrated incorrectly this maybe could be a problem like it did in my case on jos_assets.


Locked

Return to “Performance - Joomla! 2.5”