joomla/joomla-cms — pull request #5544 by pierinz (2014-12-28 16:07:13+00:00)
Title: Improve com_content/models/articles.php to speed up big sites

As the number of articles grows, the site becomes slower. Modules like mod_articles_popular are painful to load on sites with 5000+ articles.
I found that the main problem is the handling of "bad categories" (unpublished categories and their child categories), which requires too many costly joins. I split the query in two: we prefetch the bad categories' id, then we exclude them in the real query. The speed improvement is huge on big sites, and the impact on small sites is negligible.
In addition the model by default fetches all the fields, even if they are useless; I think we should fetch only the required fields or at least prevent the useless joins. I included the "ignore.*" state to fix that (see changes in the module). This is still a draft, but the result is very interesting.

Ok, these are the numbers:

**Test site with test data, "Park Blog" page:**
Shows that the impact on small sites is negligible

Unpatched, with badcats:
1- Query Time: 2.26 ms Query memory: 0.069 MB Rows returned: 2

Patched, with badcats:
1- Query Time: 0.76 ms Query memory: 0.053 MB Rows returned: 1
2- Query Time: 1.21 ms Query memory: 0.060 MB Rows returned: 2

Unpatched, no badcats:
1- Query Time: 2.00 ms Query memory: 0.069 MB Rows returned: 2

Patched, no badcats:
1- Query Time: 0.75 ms Query memory: 0.053 MB Rows returned: 0
2- Query Time: 1.31 ms Query memory: 0.060 MB Rows returned: 2

**Test site with test data, "Most read" page:**
Thanks to the "ignore" state we can crush some ms even on small sites

Unpatched:
1- Query Time: 2.40 ms Query memory: 0.083 MB Rows returned: 5

Patched:
1- Query Time: 0.81 ms Query memory: 0.067 MB Rows returned: 1
2- Query Time: 1.47 ms Query memory: 0.081 MB Rows returned: 5

See [here](https://github.com/joomla/joomla-cms/pull/4689) for further details and other tests.