[Slow Joomla] Joins are performed without indexes - Joomla! Forum - community, help and support

hello,

i've joomla websites, each 1 has ~ 50k articles.
i can see high load average caused mysql, mysqltuner shows there're lot of joins performed without indexes.
here's i've got show processlist :


sample 1:

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 j25_content a
left join j25_content_frontpage fp on fp.content_id = a.id
left join j25_categories c on c.id = a.catid
left join j25_users ua on ua.id = a.created_by
left join j25_users uam on uam.id = a.modified_by
left join (
select contact.user_id, max(contact.id) id, contact.language
from j25_contact_details contact
where contact.published = 1
group contact.user_id, contact.language) contact on contact.user_id = a.created_by
left join j25_categories parent on parent.id = c.parent_id
left join j25_content_rating v on a.id = v.content_id
left outer join (select cat.id id j25_categories cat join j25_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.catid in (837) , (a.publish_up = '0000-00-00 00:00:00' or a.publish_up <= '2012-12-07 13:42:42') , (a.publish_down = '0000-00-00 00:00:00' or a.publish_down >= '2012-12-07 13:42:42')
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 a.created desc limit 0, 6 ;


sample 2:

code: select all

select a.id, a.asset_id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, case when badcats.id null a.state else 0 end state, a.mask, 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, a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.urls, a.attribs, a.version, a.parentid, a.ordering, a.metakey, a.metadesc, a.access, a.hits, a.metadata, a.featured, a.language, a.xreference,c.title category_title, c.alias category_alias, c.access category_access,u.name author,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
from j25_content a
left join j25_categories c on c.id = a.catid
left join j25_users u on u.id = a.created_by
left join (
select contact.user_id, max(contact.id) id, contact.language
from j25_contact_details contact
where contact.published = 1
group contact.user_id, contact.language) contact on contact.user_id = a.created_by
left join j25_categories parent on parent.id = c.parent_id
left join j25_content_rating v on a.id = v.content_id
left outer join  (select cat.id id j25_categories cat join j25_categories parent on cat.lft between parent.lft , parent.rgt parent.extension = 'com_content' , parent.published <= 0 group cat.id) badcats on badcats.id = c.id
where a.id = 33966 , (a.publish_up = '0000-00-00 00:00:00' or a.publish_up <= '2012-12-07 13:28:35') , (a.publish_down = '0000-00-00 00:00:00' or a.publish_down >= '2012-12-07 13:28:35') , (a.state = 1 or a.state =2);


any idea?

50k articles lot.

big databases need equipment, whats hosting equipment in terms of spec?