php - Ridiculously slow MySQL Queries in Joomla 3.x -

before go further, let me say, i've done that's recommended when google "slow joomla" or "optimize joomla". is, site gzipped, of css , js optimized , minified, i'm not running unnecessary components, plugins, or modules (hardly any, in fact), images optimized, caching turned on (both page , progressive) , i'm on supah-fast cloud hosting rackspace, sql database on separate rackspace server.

all of that, , i'm still getting load times upwards of 10-12 seconds, as 14-15.

from joomla debug:

application 0.000 seconds (+0.000); 0.75 mb (+0.755) - afterload application 0.027 seconds (+0.027); 2.25 mb (+1.491) - afterinitialise application 0.040 seconds (+0.013); 3.26 mb (+1.010) - afterroute application 11.986 seconds (+11.947); 5.09 mb (+1.833) - afterdispatch application 12.000 seconds (+0.014); 5.63 mb (+0.539) - beforerendermodule mod_chronoforms (tip line) application 12.006 seconds (+0.005); 5.85 mb (+0.225) - afterrendermodule mod_chronoforms (tip line) application 12.008 seconds (+0.002); 5.86 mb (+0.006) - beforerendermodule mod_custom_advanced (sponsors) application 12.009 seconds (+0.002); 5.88 mb (+0.019) - afterrendermodule mod_custom_advanced (sponsors) application 12.010 seconds (+0.001); 5.87 mb (-0.006) - beforerendermodule mod_flexi_customcode (popular now) application 12.012 seconds (+0.002); 5.89 mb (+0.018) - afterrendermodule mod_flexi_customcode (popular now) application 12.012 seconds (+0.001); 5.84 mb (-0.046) - beforerendermodule mod_articles_category (featured articles) application 12.033 seconds (+0.021); 5.97 mb (+0.127) - afterrendermodule mod_articles_category (featured articles) application 12.033 seconds (+0.000); 5.96 mb (-0.014) - beforerendermodule mod_search (search) application 12.036 seconds (+0.002); 5.98 mb (+0.022) - afterrendermodule mod_search (search) application 12.036 seconds (+0.001); 5.93 mb (-0.050) - beforerendermodule mod_acymailing (acymailing module) application 12.044 seconds (+0.007); 6.44 mb (+0.507) - afterrendermodule mod_acymailing (acymailing module) application 12.157 seconds (+0.114); 6.72 mb (+0.289) - afterrender 

the (+11.947) afterdispatch tipped me off issue mysql queries, started running of long (long, long) ones through phpmyadmin.

i found queries such these (the first of loads 8 articles category-blog view -- far understand, second same search, minus limit, allow pagination) take on 2 or 3 second each complete , there 40-some-odd queries (though vast majority near unwieldy) every time page loads:

select a.id, a.title, a.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 = '0000-00-00 00:00:00' a.created else a.modified end modified, a.modified_by, uam.name modified_by_name,   case when a.publish_up = '0000-00-00 00:00:00' 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    mydatabase_content    left join mydatabase_content_frontpage fp    on fp.content_id = a.id    left join mydatabase_categories c    on c.id = a.catid    left join mydatabase_users ua    on ua.id = a.created_by    left join mydatabase_users uam    on uam.id = a.modified_by    left join ( select contact.user_id, max(contact.id) id, contact.language    mydatabase_contact_details contact    contact.published = 1    group contact.user_id, contact.language) contact    on contact.user_id = a.created_by    left join mydatabase_categories parent    on parent.id = c.parent_id    left join mydatabase_content_rating v    on a.id = v.content_id    left    outer join (select cat.id id    mydatabase_categories cat join mydatabase_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    a.access in (1,1,5)    , c.access in (1,1,5)    ,    case when badcats.id null a.state else 0 end = 1    , (a.catid = 164 or a.catid in ( select sub.id    mydatabase_categories sub    inner join mydatabase_categories    on sub.lft > this.lft    , sub.rgt < this.rgt    this.id = 164))    , (a.publish_up = '0000-00-00 00:00:00' or a.publish_up <= '2013-08-07 07:00:01')    , (a.publish_down = '0000-00-00 00:00:00' or a.publish_down >= '2013-08-07 07:00:01')    group a.id, a.title, a.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    case when a.publish_up = '0000-00-00 00:00:00' a.created else a.publish_up end desc , a.created  limit 0, 7 

---

select a.id, a.title, a.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 = '0000-00-00 00:00:00' a.created else a.modified end modified, a.modified_by, uam.name modified_by_name,   case when a.publish_up = '0000-00-00 00:00:00' 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    mydatabase_content    left join mydatabase_content_frontpage fp    on fp.content_id = a.id    left join mydatabase_categories c    on c.id = a.catid    left join mydatabase_users ua    on ua.id = a.created_by    left join mydatabase_users uam    on uam.id = a.modified_by    left join ( select contact.user_id, max(contact.id) id, contact.language    mydatabase_contact_details contact    contact.published = 1    group contact.user_id, contact.language) contact    on contact.user_id = a.created_by    left join mydatabase_categories parent    on parent.id = c.parent_id    left join mydatabase_content_rating v    on a.id = v.content_id    left    outer join (select cat.id id    mydatabase_categories cat join mydatabase_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    a.access in (1,1,5)    , c.access in (1,1,5)    ,    case when badcats.id null a.state else 0 end = 1    , (a.catid = 164 or a.catid in ( select sub.id    mydatabase_categories sub    inner join mydatabase_categories    on sub.lft > this.lft    , sub.rgt < this.rgt    this.id = 164))    , (a.publish_up = '0000-00-00 00:00:00' or a.publish_up <= '2013-08-07 07:00:01')    , (a.publish_down = '0000-00-00 00:00:00' or a.publish_down >= '2013-08-07 07:00:01')    group a.id, a.title, a.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    case when a.publish_up = '0000-00-00 00:00:00' a.created else a.publish_up end desc , a.created 

edit

here's explain first query:

and second:

my _content table little on 14,000 rows, know isn't in grand scheme of things.

/edit

has found way optimize this? i'm not opposed hacking core (i know not to, what's point of open source project if can't in , fiddle it?) if that's takes.

edit 2 - solved (sort of).

so, found this guy seems going down right path, said hell it, , tried it.

in components/com_content/models/articles.php, replaced line 431

$query->where('(a.publish_up = ' . $nulldate . ' or a.publish_up <= ' . $nowdate . ')')                 ->where('(a.publish_down = ' . $nulldate . ' or a.publish_down >= ' . $nowdate . ')'); 

with

$query->where('(a.publish_up >= date_sub(now(), interval 1 year))'); 

i know won't work everyone, breaks pagination, seems working me far (my template uses js infinite scroll solution instead of pagination). figure if looking article on year old, can use search function.

those 2 queries each take less .04 seconds complete, , afterdispatch time joomla debug down 1.469 seconds -- not optimal, number can live , continue whittle down.

i know solution pretty hacky, , won't work else, i'd love hear more ideas improving/optimizing joomla core , joomla stock queries.

thanks so!

/edit 2

as have posted here already: joomla site slow

i uncommented the

bind-address="127.0.0.1" 

setting in mysql configuration file (my.ini).

this brought increase of execution speed local joomla installation on windows 8.1.