Site running incredibly slowly intermittently. - Joomla! Forum - community, help and support

we started having issues joomla slowing down point becomes unresponsive. during troubleshooting narrowed problem down database server , found multiple processes running query below.

i'd find out if knows causes query run without usual limit clause?

on occasion restarting apache has helped resolve issue, other we're unable find solution.

it started when running 1.5 , has continued after upgrading 2.5. used spupgrade migrate content, categories , users, rest did myself.

joomla:

• version 2.5.7
• 23,000 articles
• 162 categories
• conservative caching enabled using apc
• system cache plugin enabled

active modules:

• mod_articles_latest
• mod_custom
• mod_j4age_activate
• mod_menu
• mod_search

installed components:

• acymailing
• j4age
• jcomments
• jce
• mobile joomla

the site runs on 4gb rackspace cloud server , 2gb rackspace cloud database.

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 a.created else a.modified end modified, a.modified_by, uam.name modified_by_name,case when a.publish_up = 0 a.created else a.publish_up end 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) readmore,case when badcats.id not null 0 else a.state end state,c.title category_title, c.path category_route, c.access category_access, c.alias category_alias,case when a.created_by_alias > ' ' a.created_by_alias else ua.name end author,ua.email author_email,contact.id contactid,parent.title parent_title, parent.id parent_id, parent.path parent_route, parent.alias parent_alias,round(v.rating_sum / v.rating_count, 0) rating, v.rating_count rating_count,c.published, case when badcats.id null c.published else 0 end parents_published
from abcde_content a
left join abcde_content_frontpage fp on fp.content_id = a.id
left join abcde_categories c on c.id = a.catid
left join abcde_users ua on ua.id = a.created_by
left join abcde_users uam on uam.id = a.modified_by
left join (
select contact.user_id, max(contact.id) id, contact.language
from abcde_contact_details contact
where contact.published = 1
group contact.user_id, contact.language) contact on contact.user_id = a.created_by
left join abcde_categories parent on parent.id = c.parent_id
left join abcde_content_rating v on a.id = v.content_id
left outer join (select cat.id id abcde_categories cat join abcde_categories parent on cat.lft between parent.lft , parent.rgt parent.extension = 'com_content' , parent.published != 1 group cat.id ) badcats on badcats.id = c.id
where a.access in (1,1) , c.access in (1,1) , case when badcats.id null a.state else 0 end = 1 , (a.publish_up = '0000-00-00 00:00:00' or a.publish_up <= '2012-12-03 16:50:19') , (a.publish_down = '0000-00-00 00:00:00' or a.publish_down >= '2012-12-03 16:50:19')
group 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   fp.ordering,  a.created

i experiencing significant download times low volume sites , pages.
my "gut" tells me appears if there synchronization issue of sort.
like if timing of script interaction bad.
currently server tech support researching issue well.
joomla 2.5.8 [ sites x 9]
mysql: vsn: 5.1.57
caching : appears no distinction problem.

is possible version of mysql may part of issue here?
the problem of long downloading , appears effecting 1.5 series on occasions well. [ may servers issues , not joomla ]