Before I go any further, let me just say, I've already done everything that's recommended when you google "slow joomla" or "optimize joomla". That is, my site is GZipped, all of my css and js are optimized and minified, I'm not running any unnecessary components, plugins, or modules (hardly any, in fact), my images are optimized, caching is turned on (both page and Progressive) and I'm on supah-fast cloud hosting from Rackspace, with my SQL Database on a separate Rackspace server.
All of that, and I'm still getting load times upwards of 10-12 seconds, sometimes as much 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) for afterDispatch is what tipped me off that it was probably an issue with the MySQL queries, so I started running some of the long (long, LONG) ones through PHPMyAdmin.
I found that queries such as these (the first of which loads 8 articles for the category-blog view -- as far as I understand, the second does the same search, minus the LIMIT, to allow for pagination) take over 2 or 3 second EACH to complete and there are 40-some-odd queries (though the vast majority are nowhere near as unwieldy) every time a 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' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00' 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 > ' ' 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 mydatabase_content AS a
LEFT JOIN mydatabase_content_frontpage AS fp
ON fp.content_id = a.id
LEFT JOIN mydatabase_categories AS c
ON c.id = a.catid
LEFT JOIN mydatabase_users AS ua
ON ua.id = a.created_by
LEFT JOIN mydatabase_users AS uam
ON uam.id = a.modified_by
LEFT JOIN ( SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM mydatabase_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact
ON contact.user_id = a.created_by
LEFT JOIN mydatabase_categories as parent
ON parent.id = c.parent_id
LEFT JOIN mydatabase_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM mydatabase_categories AS cat JOIN mydatabase_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
AND parent.published != 1
GROUP BY cat.id ) AS badcats
ON badcats.id = c.id
WHERE a.access IN (1,1,5)
AND c.access IN (1,1,5)
AND
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND (a.catid = 164 OR a.catid IN ( SELECT sub.id
FROM mydatabase_categories as sub
INNER JOIN mydatabase_categories as this
ON sub.lft > this.lft
AND sub.rgt < this.rgt
WHERE this.id = 164))
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2013-08-07 07:00:01')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2013-08-07 07:00:01')
GROUP BY 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 BY
CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN 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' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00' 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 > ' ' 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 mydatabase_content AS a
LEFT JOIN mydatabase_content_frontpage AS fp
ON fp.content_id = a.id
LEFT JOIN mydatabase_categories AS c
ON c.id = a.catid
LEFT JOIN mydatabase_users AS ua
ON ua.id = a.created_by
LEFT JOIN mydatabase_users AS uam
ON uam.id = a.modified_by
LEFT JOIN ( SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM mydatabase_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact
ON contact.user_id = a.created_by
LEFT JOIN mydatabase_categories as parent
ON parent.id = c.parent_id
LEFT JOIN mydatabase_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM mydatabase_categories AS cat JOIN mydatabase_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
AND parent.published != 1
GROUP BY cat.id ) AS badcats
ON badcats.id = c.id
WHERE a.access IN (1,1,5)
AND c.access IN (1,1,5)
AND
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND (a.catid = 164 OR a.catid IN ( SELECT sub.id
FROM mydatabase_categories as sub
INNER JOIN mydatabase_categories as this
ON sub.lft > this.lft
AND sub.rgt < this.rgt
WHERE this.id = 164))
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2013-08-07 07:00:01')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2013-08-07 07:00:01')
GROUP BY 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 BY
CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END DESC , a.created
EDIT
Here's the EXPLAIN for the first query:
And for the second:
My _content table is a little over 14,000 rows, which I know isn't really all that much in the grand scheme of things.
/EDIT
Has anyone found a good way to optimize this? I'm not opposed to hacking the core (I know they say not to, but what's the point of an open source project if you can't get in and fiddle with it?) if that's what it takes.
EDIT 2 - SOLVED (sort of).
So, I found this guy who seems to be going down the right path, so I said to hell with it, and tried it.
In components/com_content/models/articles.php, I 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 this won't work for everyone, as it probably breaks pagination, but it seems to be working for me so far (my template uses a js infinite scroll solution instead of pagination). I figure if anyone is looking for an article over a year old, they can use the Search function.
Those two queries now each take less that .04 seconds to complete, and the afterDispatch time from Joomla Debug is down to 1.469 seconds -- not optimal, but a number I can live with and continue to whittle down.
I know this solution is pretty hacky, and probably won't work for anyone else, so I'd love to hear more ideas about improving/optimizing the Joomla core and the Joomla stock queries.
Thanks SO!
