The Joomla! Forum ™



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.



Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 168 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next
Author Message
PostPosted: Wed Feb 08, 2012 6:11 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Fri Sep 09, 2011 1:10 pm
Posts: 12
Hi Gary,

any updates yet?


Top
 Profile  
 
PostPosted: Thu Feb 09, 2012 10:34 am 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
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.


Top
 Profile  
 
PostPosted: Thu Feb 09, 2012 10:10 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Wed Feb 01, 2012 6:32 am
Posts: 5
Location: Poland
TBone,

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


Top
 Profile  
 
PostPosted: Fri Feb 10, 2012 5:20 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
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.

Top
 Profile  
 
PostPosted: Fri Feb 10, 2012 6:21 am 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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


Top
 Profile  
 
PostPosted: Fri Feb 10, 2012 6:36 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
Gary, have you come up with any solution?


Top
 Profile  
 
PostPosted: Fri Feb 10, 2012 8:01 am 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
The query slowing things down in my case was this one:

Code:
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/tracker/?action=TrackerItemEdit&tracker_item_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.


Top
 Profile  
 
PostPosted: Fri Feb 10, 2012 5:49 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Sat Feb 11, 2012 5:20 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
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.


Top
 Profile  
 
PostPosted: Sat Feb 11, 2012 9:11 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
@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


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 2:38 am 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 2:39 am 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 7:12 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@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


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 12:21 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
TBone wrote:
The query slowing things down in my case was this one:

Code:
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/tracker/?action=TrackerItemEdit&tracker_item_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:
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


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 2:25 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Sun Feb 12, 2012 4:28 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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 [drive] 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.


Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 12:24 am 
Joomla! Intern
Joomla! Intern

Joined: Sat Mar 29, 2008 3:52 pm
Posts: 74
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.


Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 12:55 am 
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Sat Dec 17, 2011 7:45 pm
Posts: 112
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.


Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 6:55 am 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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.

Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 8:04 am 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
On what type of page is this query fired?


Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 1:37 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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


Top
 Profile  
 
PostPosted: Mon Feb 13, 2012 4:10 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Tue Feb 14, 2012 8:45 am 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
Code:
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$'.


Top
 Profile  
 
PostPosted: Tue Feb 14, 2012 2:01 pm 
Joomla! Ace
Joomla! Ace

Joined: Sat Jan 21, 2006 8:42 pm
Posts: 1248
Has someone tested if a STRAIGHT_JOIN helps with those queries?


Top
 Profile  
 
PostPosted: Tue Feb 14, 2012 4:27 pm 
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Sat Dec 17, 2011 7:45 pm
Posts: 112
Has any cache extensions or settings helped you guys out at all?


Top
 Profile  
 
PostPosted: Tue Feb 14, 2012 10:18 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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 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


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 4:24 am 
Joomla! Ace
Joomla! Ace

Joined: Sat Jan 21, 2006 8:42 pm
Posts: 1248
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.


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 7:39 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
tappy52dog wrote:
Has any cache extensions or settings helped you guys out at all?


Nothing helped me.


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 7:54 am 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 8:04 am 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
Ah ok.
I tried running the query again in phpmyadmin
I modified all the N's

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 168 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next



Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group