150k+ articles / 1700 categories performance improvements

[Ruud68]

Hi,
so I have the opportunity to migrate a J3 site with over 150000 articles in 1705 categories to Joomla 5.
performance of the current site is very bad: the customer has a lot of caching / mysql tuning in place on his 12VCPU / 64GB VPS.

Due to the bad performance with these number of articles, I needed to investigate if the move to J5 with PHP8.x and newer MariaDB would do anything on the performance: if not, Joomla is not up for the job as we cannot keep adding more hardware just to keep the site up.

So I started with the (test) upgrade:

1. remove all extensions and only keep core to be able to upgrade from J3 to J4 and next to J5
2. start upgrade

So here was issue number 1: the upgrade of the database timed out and was not able to complete: the queries executed just took to long and as some rolled back, some where stuck in an intermediate state.

so next:

1. restore J3 site
2. remove all extensions
3. purge #__content table (make a sql dump first)
4. start upgrade
5. set mysql in sql_mode="" (to be able to import the date values of 0000-00-00 00:00:00 where the now are NULL
6. add xreference varchar(50) to the #__content table
7. use 'bigdump.php' to import the 3.9GB #__content sql dump
8. run queries to change the 0000-00-00 00:00:00 dates to NULL using limit 50000 and then run them multiple times to avoid timeouts
9. remove the xreference field

So now I have a 'clean' Joomla 5 site with all content on it. it is on a very basic 1 VCPU/1GB VPS.

Now for the test:

1. I created a frontpage > index.php?option=com_content&view=categories&id=0 (so a categories view listing all categories)
2. I created some test PHP code that loads 200 articles via the ArticleModel

    $start = \time();
    $articlesModel = new \Joomla\Component\Content\Site\Model\ArticlesModel(['ignore_request' => true]);
    $params = \Joomla\CMS\Factory::getApplication()->getParams();
    echo '<pre>' . time() - $start . '</pre>';
    $articlesModel->setState('params', $params);
    $articlesModel->setState('list.limit', 200);
    echo '<pre>Articles:  ' . \count($articlesModel->getItems()) . '</pre>';
    echo '<pre>Time (seconds): ' . time() - $start . '</pre>';

the load times:

1. Frontpage: 20 seconds to display the categories
2. 200 articles: 94 seconds

I was happy that at least the server didn't crash under the load, so had my hopes up and started analyzing the load times and if they could be altered.

I added the following index: CREATE INDEX idx_catid_state ON #__content (catid, state);

The frontpage now loaded within 1 second! This is just by adding this index!

The query created by the article model was a little bit more challenging 'explain' learned me:

• MySQL’s optimizer thought it would be faster to read #__categories (c) first.
• But reading c first means more rows scanned and prevents using the best covering index on #__content.
• So MySQL did extra work: full scans, temp tables, filesorts.

So basically regardless of the number of articles needed to return it always had to go though the 150K articles to get the order

In order to instruct MySQL to stick to the order provided (#__content first!) I needed to do instead of a LEFT JOIN on the categories table a STRAIGHT_JOIN

as that is not provided by the Joomla DatabaseDriver I worked around it by altering the code in the site ArticleModel getListQuery() funtion to:

//            ->from($db->quoteName('#__content', 'a'))
            ->from(
                       $db->quoteName('#__content', 'a')
                      . ' STRAIGHT_JOIN ' . $db->quoteName('#__categories', 'c')
                      . ' ON ' . $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
           )
//            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))
            ->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('a.created_by'))
            ->join('LEFT', $db->quoteName('#__users', 'uam'), $db->quoteName('uam.id') . ' = ' . $db->quoteName('a.modified_by'))
            ->join('LEFT', $db->quoteName('#__categories', 'parent'), $db->quoteName('parent.id') . ' = ' . $db->quoteName('c.parent_id'));

So basically commenting out the from and the join for the categories and adding a new ->from (with STRAIGHT_JOIN for the categories).

and for the test: the loading time for the 200 articles was down from 94 seconds to 5 seconds (!!!!!)

So basically now we are on a minimum vps and it is able to (almost) outperform the 12CPU VPS with these small changes.

So what is next:
The index I from now on will always do on any site I manage / develop, but IMO it would be a good addition for a discussion to have it in Core

The change to the ArticleModel introducing the STRAIGHT_JOIN I can do with a model override to avoid falling back to the slow query when Joomla updates, but I think this would be a good point for a discussion.

These are just two changes that already make a world of difference and I can probably find more (e.g. when going in the back-end to the Articles, it loads forever before it displays the articles, I yet need to investigate if that can be speeded up as well (but that currently has no priority for me).

I think it would be good to introduce a ->join('STRAIGHT', ....) and adjust the ArticlesModel (and maybe others) to start using that. Knowing that progress and MSSQL do not support STRAIGHT_JOIN there should be for these a fall back to INNER or LEFT.

And just to state my case here: I know that there is no issue and that it is able to perform just fine by adding extra hardware, but imagine what the impact would be of needing less hardware to get the same speed: less $$$, less environmental impact, etc.

So the +15 times speed increase is likely a lot and specifically for sites with these amounts of articles / categories, but is we can shave off even 10% of the performance we still have a good improvement

@HLeithner

[Fedik]

You can follow one of existing issue:

• [6.1] Improve article list performance for large sites #45542
• [5.4] [WiP] Improve articles lists in backend for display of and filtering by missing workflow associations + better performance #45660
• [6.1] [Performance] Access Rules smart preload, reduce memory and time use, for large sites #44078

and to a few linked to those.

[Ruud68]

Thanks @Fedik , did a quick read up and used the #45542 (the site ArticlesModel change) from @SniperSister
result before: 86 seconds
result after: 93 seconds

The other PR's I haven't tested as these are mainly for back-end.

@SniperSister could you test with change suggested by me to see if that will also make your (front-end) articles list etc. faster?
so in ./component/com_content/src/Model/ArticlesModel
replace in function getListQuery():

->from($db->quoteName('#__content', 'a'))
            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))

with:

            ->from(
                       $db->quoteName('#__content', 'a')
                      . ' STRAIGHT_JOIN ' . $db->quoteName('#__categories', 'c')
                      . ' ON ' . $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid')
           )

[Ruud68]

So just back from holidays, was hoping that this contribution would get some traction in the mean time.
Is this not the correct place to discuss / propose / contribute to the Joomla Open Source project? I'm not on mattermost and although that says "With Mattermost we’re able to open up the channels, which means that all channels which don’t include security or privacy-related information will be publicly accessible", I cannot see what is being discussed there as it actually is closed behind a login. Is this contribution being discussed somewhere?

What is needed to get this thing moving? Or is 'the silence' the way of saying no thank you?

[HLeithner]

The issue is that we are all volunteers and it might take some time to get back to things. Since real life and job might be time consuming, it often is not possible to work on things as much as I like or others like.

I hope I will find some time in the near future

[HLeithner]

maybe @richard67 could have a look here too

[Ruud68]

Thanks @HLeithner and I totally understand as I am a volunteer myself here as well where I have also to balance my available time.

And just to give some (generic) feedback: Checking if there is follow up, wondering why it is not, pondering on how to get traction is 'costing' me also a lot of time (and energy), time better spend elsewhere.

I think that the "Joomla! Volunteer Engagement Team (VET)" could / should play an active role here. If there is one thing 'preventing' me (as a developer) from contributing to Joomla it is the 'lack of interest / interaction / time'.

[Ruud68]

So the J3 site keeps growing steadily, was able to adjust the administrator ArticlesModel to load 200K articles from 1 minute time to 5 seconds time by changing the 'order' logic by (which makes the whole query resort to a very sqlslow file sort)

While this is being discussed (somewhere I assume) I want to make headway by implementing and 'battle' testing my changes in a custom ArticlesModel that I can use instead of changing the core files.

Can somebody of the community tell me if it is possible to do an override of the com_content administrator ArticlesModel and if this is possible, a hint on how to tackle this would be great!

Got it to the point that it loads my custom ArticleModel, but because that needs to extend the original ArticlesModel it doesn't work (as the extends ArticleModel then basically extends itself which is not possible). So this is probably a dead-end.

So maybe via the container?

[joomdonation]

@Ruud68 Try https://github.com/SharkyKZ/joomla-mvc-override-plugin or https://obix.nl/en/documentation/extensions/class-extender , maybe it would help.

[sergeytolkachyov]

Can somebody of the community tell me if it is possible to do an override of the com_content administrator ArticlesModel and if this is possible, a hint on how to tackle this would be great!

Got it to the point that it loads my custom ArticleModel, but because that needs to extend the original ArticlesModel it doesn't work (as the extends ArticleModel then basically extends itself which is not possible). So this is probably a dead-end.

So maybe via the container?

https://github.com/WebTolk/wt-multicategories-joomla-plugin I tried to override original ArticlesModel for multicategories. It turned out to be not a good idea, but the model override itself works.

[Ruud68]

hi @joomdonation thank you answering and for the links! Will check them out and hope they (still) work with J5/6.

[Ruud68]

Just to follow up on this:
did a small / preliminary refactoring of the query for the administrator ArticlesModel:
The query for 150619 articles now takes 85.44MS where it was 53.06 Seconds (!) an improvement of over 600 times! (a reduction by approximately 99.84%)
So the biggest delay now on that page is actually not from fetching the articles to display, but from getting the total count (which is next to improve: That query still takes 6 seconds.

Before:

After:

[SniperSister]

@Ruud68 that's a fanstastic result! Could you create a PR with these optimizations?

[Ruud68]

@SniperSister this is work in progress, let me explain the logic:

So the ArticlesModel creates a complex query that basically cannot be sorted with the use of an index: this means that the whole result is saved to a temp file and then sorted, just to then get 20 records from that 'expensive' set.

In the result above I 'skipped' the sorting altogether which made it very fast, but that lost the ability to sort on the columns.

So I refactored, just as a proof of concept: my time is up for this :S), now with the ability to sort:
result after:

5 seconds (opposed to the normal 60+ seconds), still workable!

here is the code (for J5.3.4) : replace in ./administrator/components/com_content/src/Model/ArticlesModel.php the 'protected function getListQuery()' with the following two (!) functions:

    protected function getListQuery()
    {
        // Create a new query object.
        $db    = $this->getDatabase();
        $query = $db->getQuery(true);
        $user  = $this->getCurrentUser();
        $params = ComponentHelper::getParams('com_content');

        // Select the required fields from the table.
        $query->select(
            $this->getState(
                'list.select',
                [
                    $db->quoteName('a.id'),
                    $db->quoteName('a.asset_id'),
                    $db->quoteName('a.title'),
                    $db->quoteName('a.alias'),
                    $db->quoteName('a.checked_out'),
                    $db->quoteName('a.checked_out_time'),
                    $db->quoteName('a.catid'),
                    $db->quoteName('a.state'),
                    $db->quoteName('a.access'),
                    $db->quoteName('a.created'),
                    $db->quoteName('a.created_by'),
                    $db->quoteName('a.created_by_alias'),
                    $db->quoteName('a.modified'),
                    $db->quoteName('a.ordering'),
                    $db->quoteName('a.featured'),
                    $db->quoteName('a.language'),
                    $db->quoteName('a.hits'),
                    $db->quoteName('a.publish_up'),
                    $db->quoteName('a.publish_down'),
                    $db->quoteName('a.introtext'),
                    $db->quoteName('a.fulltext'),
                    $db->quoteName('a.note'),
                    $db->quoteName('a.images'),
                    $db->quoteName('a.metakey'),
                    $db->quoteName('a.metadesc'),
                    $db->quoteName('a.metadata'),
                    $db->quoteName('a.version'),
                ]
            )
        )
            ->select(
                [
                    $db->quoteName('fp.featured_up'),
                    $db->quoteName('fp.featured_down'),
                    $db->quoteName('l.title', 'language_title'),
                    $db->quoteName('l.image', 'language_image'),
                    $db->quoteName('uc.name', 'editor'),
                    $db->quoteName('ag.title', 'access_level'),
                    $db->quoteName('c.title', 'category_title'),
                    $db->quoteName('c.created_user_id', 'category_uid'),
                    $db->quoteName('c.level', 'category_level'),
                    $db->quoteName('c.published', 'category_published'),
                    $db->quoteName('parent.title', 'parent_category_title'),
                    $db->quoteName('parent.id', 'parent_category_id'),
                    $db->quoteName('parent.created_user_id', 'parent_category_uid'),
                    $db->quoteName('parent.level', 'parent_category_level'),
                    $db->quoteName('ua.name', 'author_name'),
                    $db->quoteName('wa.stage_id', 'stage_id'),
                    $db->quoteName('ws.title', 'stage_title'),
                    $db->quoteName('ws.workflow_id', 'workflow_id'),
                    $db->quoteName('w.title', 'workflow_title'),
                ]
            );

        $orderCol  = $this->state->get('list.ordering', 'a.id');
        $orderDirn = $this->state->get('list.direction', 'DESC');
        $listLimit = $this->getState('list.limit', 20);

        if ($orderCol === 'a.ordering' || $orderCol === 'category_title') {
            $ordering = [
                $db->quoteName('c.title') . ' ' . $db->escape($orderDirn),
                $db->quoteName('a.ordering') . ' ' . $db->escape($orderDirn),
            ];
        } else {
            $ordering = $db->escape($orderCol) . ' ' . $db->escape($orderDirn);
        }

        $subListQuery = $this->getSubListQuery();
        $subListQuery->order($ordering);
        $subListQuery->setLimit($listLimit);

        $query->from('(' . $subListQuery . ') AS slq')
            ->join('INNER', $db->quoteName('#__content', 'a'), $db->quoteName('a.id') . ' = ' . $db->quoteName('slq.id'));

        $query->where($db->quoteName('wa.extension') . ' = ' . $db->quote('com_content.article'))
            ->join('LEFT', $db->quoteName('#__languages', 'l'), $db->quoteName('l.lang_code') . ' = ' . $db->quoteName('a.language'))
            ->join('LEFT', $db->quoteName('#__content_frontpage', 'fp'), $db->quoteName('fp.content_id') . ' = ' . $db->quoteName('a.id'))
            ->join('LEFT', $db->quoteName('#__users', 'uc'), $db->quoteName('uc.id') . ' = ' . $db->quoteName('a.checked_out'))
            ->join('LEFT', $db->quoteName('#__viewlevels', 'ag'), $db->quoteName('ag.id') . ' = ' . $db->quoteName('a.access'))
            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))
            ->join('LEFT', $db->quoteName('#__categories', 'parent'), $db->quoteName('parent.id') . ' = ' . $db->quoteName('c.parent_id'))
            ->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('a.created_by'))
            ->join('INNER', $db->quoteName('#__workflow_associations', 'wa'), $db->quoteName('wa.item_id') . ' = ' . $db->quoteName('a.id'))
            ->join('INNER', $db->quoteName('#__workflow_stages', 'ws'), $db->quoteName('ws.id') . ' = ' . $db->quoteName('wa.stage_id'))
            ->join('INNER', $db->quoteName('#__workflows', 'w'), $db->quoteName('w.id') . ' = ' . $db->quoteName('ws.workflow_id'));

        if (PluginHelper::isEnabled('content', 'vote')) {
            $query->select(
                [
                    'COALESCE(NULLIF(ROUND(' . $db->quoteName('v.rating_sum') . ' / ' . $db->quoteName('v.rating_count') . ', 0), 0), 0)'
                        . ' AS ' . $db->quoteName('rating'),
                    'COALESCE(NULLIF(' . $db->quoteName('v.rating_count') . ', 0), 0) AS ' . $db->quoteName('rating_count'),
                ]
            )
                ->join('LEFT', $db->quoteName('#__content_rating', 'v'), $db->quoteName('a.id') . ' = ' . $db->quoteName('v.content_id'));
        }

        // Join over the associations.
        if (Associations::isEnabled()) {
            $subQuery = $db->getQuery(true)
                ->select('COUNT(' . $db->quoteName('asso1.id') . ') > 1')
                ->from($db->quoteName('#__associations', 'asso1'))
                ->join('INNER', $db->quoteName('#__associations', 'asso2'), $db->quoteName('asso1.key') . ' = ' . $db->quoteName('asso2.key'))
                ->where(
                    [
                        $db->quoteName('asso1.id') . ' = ' . $db->quoteName('a.id'),
                        $db->quoteName('asso1.context') . ' = ' . $db->quote('com_content.item'),
                    ]
                );

            $query->select('(' . $subQuery . ') AS ' . $db->quoteName('association'));
        }

        // Filter by access level.
        $access = $this->getState('filter.access');

        if (is_numeric($access)) {
            $access = (int) $access;
            $query->where($db->quoteName('a.access') . ' = :access')
                ->bind(':access', $access, ParameterType::INTEGER);
        } elseif (\is_array($access)) {
            $access = ArrayHelper::toInteger($access);
            $query->whereIn($db->quoteName('a.access'), $access);
        }

        // Filter by featured.
        $featured = (string) $this->getState('filter.featured');

        if (\in_array($featured, ['0', '1'])) {
            $featured = (int) $featured;
            $query->where($db->quoteName('a.featured') . ' = :featured')
                ->bind(':featured', $featured, ParameterType::INTEGER);
        }

        // Filter by access level on categories.
        if (!$user->authorise('core.admin')) {
            $groups = $user->getAuthorisedViewLevels();
            $query->whereIn($db->quoteName('a.access'), $groups);
            $query->whereIn($db->quoteName('c.access'), $groups);
        }

        // Filter by published state
        $workflowStage = (string) $this->getState('filter.stage');

        if ($params->get('workflow_enabled') && is_numeric($workflowStage)) {
            $workflowStage = (int) $workflowStage;
            $query->where($db->quoteName('wa.stage_id') . ' = :stage')
                ->bind(':stage', $workflowStage, ParameterType::INTEGER);
        }

        $published = (string) $this->getState('filter.published');

        if ($published !== '*') {
            if (is_numeric($published)) {
                $state = (int) $published;
                $query->where($db->quoteName('a.state') . ' = :state')
                    ->bind(':state', $state, ParameterType::INTEGER);
            } elseif (!is_numeric($workflowStage)) {
                $query->whereIn(
                    $db->quoteName('a.state'),
                    [
                        ContentComponent::CONDITION_PUBLISHED,
                        ContentComponent::CONDITION_UNPUBLISHED,
                    ]
                );
            }
        }

        // Filter by categories and by level
        $categoryId = $this->getState('filter.category_id', []);
        $level      = (int) $this->getState('filter.level');

        if (!\is_array($categoryId)) {
            $categoryId = $categoryId ? [$categoryId] : [];
        }

        // Case: Using both categories filter and by level filter
        if (\count($categoryId)) {
            $categoryId       = ArrayHelper::toInteger($categoryId);
            $categoryTable    = Table::getInstance('Category', '\\Joomla\\CMS\\Table\\');
            $subCatItemsWhere = [];

            foreach ($categoryId as $key => $filter_catid) {
                $categoryTable->load($filter_catid);

                // Because values to $query->bind() are passed by reference, using $query->bindArray() here instead to prevent overwriting.
                $valuesToBind = [$categoryTable->lft, $categoryTable->rgt];

                if ($level) {
                    $valuesToBind[] = $level + $categoryTable->level - 1;
                }

                // Bind values and get parameter names.
                $bounded = $query->bindArray($valuesToBind);

                $categoryWhere = $db->quoteName('c.lft') . ' >= ' . $bounded[0] . ' AND ' . $db->quoteName('c.rgt') . ' <= ' . $bounded[1];

                if ($level) {
                    $categoryWhere .= ' AND ' . $db->quoteName('c.level') . ' <= ' . $bounded[2];
                }

                $subCatItemsWhere[] = '(' . $categoryWhere . ')';
            }

            $query->where('(' . implode(' OR ', $subCatItemsWhere) . ')');
        } elseif ($level = (int) $level) {
            // Case: Using only the by level filter
            $query->where($db->quoteName('c.level') . ' <= :level')
                ->bind(':level', $level, ParameterType::INTEGER);
        }

        // Filter by author
        $authorId = $this->getState('filter.author_id');

        if (is_numeric($authorId)) {
            $authorId = (int) $authorId;
            $type     = $this->getState('filter.author_id.include', true) ? ' = ' : ' <> ';
            $query->where($db->quoteName('a.created_by') . $type . ':authorId')
                ->bind(':authorId', $authorId, ParameterType::INTEGER);
        } elseif (\is_array($authorId)) {
            // Check to see if by_me is in the array
            if (\in_array('by_me', $authorId)) {
                // Replace by_me with the current user id in the array
                $authorId['by_me'] = $user->id;
            }

            $authorId = ArrayHelper::toInteger($authorId);
            $query->whereIn($db->quoteName('a.created_by'), $authorId);
        }

        // Filter by search in title.
        $search = $this->getState('filter.search');

        if (!empty($search)) {
            if (stripos($search, 'id:') === 0) {
                $search = (int) substr($search, 3);
                $query->where($db->quoteName('a.id') . ' = :search')
                    ->bind(':search', $search, ParameterType::INTEGER);
            } elseif (stripos($search, 'author:') === 0) {
                $search = '%' . substr($search, 7) . '%';
                $query->where('(' . $db->quoteName('ua.name') . ' LIKE :search1 OR ' . $db->quoteName('ua.username') . ' LIKE :search2)')
                    ->bind([':search1', ':search2'], $search);
            } elseif (stripos($search, 'content:') === 0) {
                $search = '%' . substr($search, 8) . '%';
                $query->where('(' . $db->quoteName('a.introtext') . ' LIKE :search1 OR ' . $db->quoteName('a.fulltext') . ' LIKE :search2)')
                    ->bind([':search1', ':search2'], $search);
            } elseif (stripos($search, 'checkedout:') === 0) {
                $search = '%' . substr($search, 11) . '%';
                $query->where('(' . $db->quoteName('uc.name') . ' LIKE :search1 OR ' . $db->quoteName('uc.username') . ' LIKE :search2)'
                    . ' AND ' . $db->quoteName('a.checked_out') . ' IS NOT NULL')
                    ->bind([':search1', ':search2'], $search);
            } else {
                $search = '%' . str_replace(' ', '%', trim($search)) . '%';
                $query->where(
                    '(' . $db->quoteName('a.title') . ' LIKE :search1 OR ' . $db->quoteName('a.alias') . ' LIKE :search2'
                        . ' OR ' . $db->quoteName('a.note') . ' LIKE :search3)'
                )
                    ->bind([':search1', ':search2', ':search3'], $search);
            }
        }

        // Filter on the language.
        if ($language = $this->getState('filter.language')) {
            $query->where($db->quoteName('a.language') . ' = :language')
                ->bind(':language', $language);
        }

        // Filter by a single or group of tags.
        $tag = $this->getState('filter.tag');

        // Run simplified query when filtering by one tag.
        if (\is_array($tag) && \count($tag) === 1) {
            $tag = $tag[0];
        }

        if ($tag && \is_array($tag)) {
            $tag = ArrayHelper::toInteger($tag);

            $subQuery = $db->getQuery(true)
                ->select('DISTINCT ' . $db->quoteName('content_item_id'))
                ->from($db->quoteName('#__contentitem_tag_map'))
                ->where(
                    [
                        $db->quoteName('tag_id') . ' IN (' . implode(',', $query->bindArray($tag)) . ')',
                        $db->quoteName('type_alias') . ' = ' . $db->quote('com_content.article'),
                    ]
                );

            $query->join(
                'INNER',
                '(' . $subQuery . ') AS ' . $db->quoteName('tagmap'),
                $db->quoteName('tagmap.content_item_id') . ' = ' . $db->quoteName('a.id')
            );
        } elseif ($tag = (int) $tag) {
            $query->join(
                'INNER',
                $db->quoteName('#__contentitem_tag_map', 'tagmap'),
                $db->quoteName('tagmap.content_item_id') . ' = ' . $db->quoteName('a.id')
            )
                ->where(
                    [
                        $db->quoteName('tagmap.tag_id') . ' = :tag',
                        $db->quoteName('tagmap.type_alias') . ' = ' . $db->quote('com_content.article'),
                    ]
                )
                ->bind(':tag', $tag, ParameterType::INTEGER);
        }

        return $query;
    }

    protected function getSubListQuery()
    {
        // Create a new query object.
        $db    = $this->getDatabase();
        $query = $db->createQuery();
        $user  = $this->getCurrentUser();

        $params = ComponentHelper::getParams('com_content');

        // Select the required fields from the table.
        $query->select($db->quoteName('a.id'))
            ->from($db->quoteName('#__content', 'a'))
            ->where($db->quoteName('wa.extension') . ' = ' . $db->quote('com_content.article'))
            ->join('LEFT', $db->quoteName('#__languages', 'l'), $db->quoteName('l.lang_code') . ' = ' . $db->quoteName('a.language'))
            ->join('LEFT', $db->quoteName('#__content_frontpage', 'fp'), $db->quoteName('fp.content_id') . ' = ' . $db->quoteName('a.id'))
            ->join('LEFT', $db->quoteName('#__users', 'uc'), $db->quoteName('uc.id') . ' = ' . $db->quoteName('a.checked_out'))
            ->join('LEFT', $db->quoteName('#__viewlevels', 'ag'), $db->quoteName('ag.id') . ' = ' . $db->quoteName('a.access'))
            ->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'))
            ->join('LEFT', $db->quoteName('#__categories', 'parent'), $db->quoteName('parent.id') . ' = ' . $db->quoteName('c.parent_id'))
            ->join('LEFT', $db->quoteName('#__users', 'ua'), $db->quoteName('ua.id') . ' = ' . $db->quoteName('a.created_by'))
            ->join('INNER', $db->quoteName('#__workflow_associations', 'wa'), $db->quoteName('wa.item_id') . ' = ' . $db->quoteName('a.id'))
            ->join('INNER', $db->quoteName('#__workflow_stages', 'ws'), $db->quoteName('ws.id') . ' = ' . $db->quoteName('wa.stage_id'))
            ->join('INNER', $db->quoteName('#__workflows', 'w'), $db->quoteName('w.id') . ' = ' . $db->quoteName('ws.workflow_id'));

        if (PluginHelper::isEnabled('content', 'vote')) {
            $query->select(
                [
                    'COALESCE(NULLIF(ROUND(' . $db->quoteName('v.rating_sum') . ' / ' . $db->quoteName('v.rating_count') . ', 0), 0), 0)'
                        . ' AS ' . $db->quoteName('rating'),
                    'COALESCE(NULLIF(' . $db->quoteName('v.rating_count') . ', 0), 0) AS ' . $db->quoteName('rating_count'),
                ]
            )
                ->join('LEFT', $db->quoteName('#__content_rating', 'v'), $db->quoteName('a.id') . ' = ' . $db->quoteName('v.content_id'));
        }

        // Join over the associations.
        if (Associations::isEnabled()) {
            $subQuery = $db->getQuery(true)
                ->select('COUNT(' . $db->quoteName('asso1.id') . ') > 1')
                ->from($db->quoteName('#__associations', 'asso1'))
                ->join('INNER', $db->quoteName('#__associations', 'asso2'), $db->quoteName('asso1.key') . ' = ' . $db->quoteName('asso2.key'))
                ->where(
                    [
                        $db->quoteName('asso1.id') . ' = ' . $db->quoteName('a.id'),
                        $db->quoteName('asso1.context') . ' = ' . $db->quote('com_content.item'),
                    ]
                );

            $query->select('(' . $subQuery . ') AS ' . $db->quoteName('association'));
        }

        // Filter by access level.
        $access = $this->getState('filter.access');

        if (is_numeric($access)) {
            $access = (int) $access;
            $query->where($db->quoteName('a.access') . ' = :access')
                ->bind(':access', $access, ParameterType::INTEGER);
        } elseif (\is_array($access)) {
            $access = ArrayHelper::toInteger($access);
            $query->whereIn($db->quoteName('a.access'), $access);
        }

        // Filter by featured.
        $featured = (string) $this->getState('filter.featured');

        if (\in_array($featured, ['0', '1'])) {
            $featured = (int) $featured;
            $query->where($db->quoteName('a.featured') . ' = :featured')
                ->bind(':featured', $featured, ParameterType::INTEGER);
        }

        // Filter by access level on categories.
        if (!$user->authorise('core.admin')) {
            $groups = $user->getAuthorisedViewLevels();
            $query->whereIn($db->quoteName('a.access'), $groups);
            $query->whereIn($db->quoteName('c.access'), $groups);
        }

        // Filter by published state
        $workflowStage = (string) $this->getState('filter.stage');

        if ($params->get('workflow_enabled') && is_numeric($workflowStage)) {
            $workflowStage = (int) $workflowStage;
            $query->where($db->quoteName('wa.stage_id') . ' = :stage')
                ->bind(':stage', $workflowStage, ParameterType::INTEGER);
        }

        $published = (string) $this->getState('filter.published');

        if ($published !== '*') {
            if (is_numeric($published)) {
                $state = (int) $published;
                $query->where($db->quoteName('a.state') . ' = :state')
                    ->bind(':state', $state, ParameterType::INTEGER);
            } elseif (!is_numeric($workflowStage)) {
                $query->whereIn(
                    $db->quoteName('a.state'),
                    [
                        ContentComponent::CONDITION_PUBLISHED,
                        ContentComponent::CONDITION_UNPUBLISHED,
                    ]
                );
            }
        }

        // Filter by categories and by level
        $categoryId = $this->getState('filter.category_id', []);
        $level      = (int) $this->getState('filter.level');

        if (!\is_array($categoryId)) {
            $categoryId = $categoryId ? [$categoryId] : [];
        }

        // Case: Using both categories filter and by level filter
        if (\count($categoryId)) {
            $categoryId       = ArrayHelper::toInteger($categoryId);
            $categoryTable    = Table::getInstance('Category', '\\Joomla\\CMS\\Table\\');
            $subCatItemsWhere = [];

            foreach ($categoryId as $key => $filter_catid) {
                $categoryTable->load($filter_catid);

                // Because values to $query->bind() are passed by reference, using $query->bindArray() here instead to prevent overwriting.
                $valuesToBind = [$categoryTable->lft, $categoryTable->rgt];

                if ($level) {
                    $valuesToBind[] = $level + $categoryTable->level - 1;
                }

                // Bind values and get parameter names.
                $bounded = $query->bindArray($valuesToBind);

                $categoryWhere = $db->quoteName('c.lft') . ' >= ' . $bounded[0] . ' AND ' . $db->quoteName('c.rgt') . ' <= ' . $bounded[1];

                if ($level) {
                    $categoryWhere .= ' AND ' . $db->quoteName('c.level') . ' <= ' . $bounded[2];
                }

                $subCatItemsWhere[] = '(' . $categoryWhere . ')';
            }

            $query->where('(' . implode(' OR ', $subCatItemsWhere) . ')');
        } elseif ($level = (int) $level) {
            // Case: Using only the by level filter
            $query->where($db->quoteName('c.level') . ' <= :level')
                ->bind(':level', $level, ParameterType::INTEGER);
        }

        // Filter by author
        $authorId = $this->getState('filter.author_id');

        if (is_numeric($authorId)) {
            $authorId = (int) $authorId;
            $type     = $this->getState('filter.author_id.include', true) ? ' = ' : ' <> ';
            $query->where($db->quoteName('a.created_by') . $type . ':authorId')
                ->bind(':authorId', $authorId, ParameterType::INTEGER);
        } elseif (\is_array($authorId)) {
            // Check to see if by_me is in the array
            if (\in_array('by_me', $authorId)) {
                // Replace by_me with the current user id in the array
                $authorId['by_me'] = $user->id;
            }

            $authorId = ArrayHelper::toInteger($authorId);
            $query->whereIn($db->quoteName('a.created_by'), $authorId);
        }

        // Filter by search in title.
        $search = $this->getState('filter.search');

        if (!empty($search)) {
            if (stripos($search, 'id:') === 0) {
                $search = (int) substr($search, 3);
                $query->where($db->quoteName('a.id') . ' = :search')
                    ->bind(':search', $search, ParameterType::INTEGER);
            } elseif (stripos($search, 'author:') === 0) {
                $search = '%' . substr($search, 7) . '%';
                $query->where('(' . $db->quoteName('ua.name') . ' LIKE :search1 OR ' . $db->quoteName('ua.username') . ' LIKE :search2)')
                    ->bind([':search1', ':search2'], $search);
            } elseif (stripos($search, 'content:') === 0) {
                $search = '%' . substr($search, 8) . '%';
                $query->where('(' . $db->quoteName('a.introtext') . ' LIKE :search1 OR ' . $db->quoteName('a.fulltext') . ' LIKE :search2)')
                    ->bind([':search1', ':search2'], $search);
            } elseif (stripos($search, 'checkedout:') === 0) {
                $search = '%' . substr($search, 11) . '%';
                $query->where('(' . $db->quoteName('uc.name') . ' LIKE :search1 OR ' . $db->quoteName('uc.username') . ' LIKE :search2)'
                    . ' AND ' . $db->quoteName('a.checked_out') . ' IS NOT NULL')
                    ->bind([':search1', ':search2'], $search);
            } else {
                $search = '%' . str_replace(' ', '%', trim($search)) . '%';
                $query->where(
                    '(' . $db->quoteName('a.title') . ' LIKE :search1 OR ' . $db->quoteName('a.alias') . ' LIKE :search2'
                        . ' OR ' . $db->quoteName('a.note') . ' LIKE :search3)'
                )
                    ->bind([':search1', ':search2', ':search3'], $search);
            }
        }

        // Filter on the language.
        if ($language = $this->getState('filter.language')) {
            $query->where($db->quoteName('a.language') . ' = :language')
                ->bind(':language', $language);
        }

        // Filter by a single or group of tags.
        $tag = $this->getState('filter.tag');

        // Run simplified query when filtering by one tag.
        if (\is_array($tag) && \count($tag) === 1) {
            $tag = $tag[0];
        }

        if ($tag && \is_array($tag)) {
            $tag = ArrayHelper::toInteger($tag);

            $subQuery = $db->getQuery(true)
                ->select('DISTINCT ' . $db->quoteName('content_item_id'))
                ->from($db->quoteName('#__contentitem_tag_map'))
                ->where(
                    [
                        $db->quoteName('tag_id') . ' IN (' . implode(',', $query->bindArray($tag)) . ')',
                        $db->quoteName('type_alias') . ' = ' . $db->quote('com_content.article'),
                    ]
                );

            $query->join(
                'INNER',
                '(' . $subQuery . ') AS ' . $db->quoteName('tagmap'),
                $db->quoteName('tagmap.content_item_id') . ' = ' . $db->quoteName('a.id')
            );
        } elseif ($tag = (int) $tag) {
            $query->join(
                'INNER',
                $db->quoteName('#__contentitem_tag_map', 'tagmap'),
                $db->quoteName('tagmap.content_item_id') . ' = ' . $db->quoteName('a.id')
            )
                ->where(
                    [
                        $db->quoteName('tagmap.tag_id') . ' = :tag',
                        $db->quoteName('tagmap.type_alias') . ' = ' . $db->quote('com_content.article'),
                    ]
                )
                ->bind(':tag', $tag, ParameterType::INTEGER);
        }

        return $query;
    }

[zinshuke]

#42313 — same issue.
Very disappointed with Joomla’s performance on large sites.
In my case, I had to create a new component as a workaround. My solution only selects the IDs of the content first, and then fetches the details later.

[Ruud68]

Hi, only (pre) selecting the ID's is what gives the best performance, but that comes at a price: filtering and sorting.

The way I see it: in the (very) 'old' days you had Volkswagen and they where performing best in line. If a competitor came that challenged their GTI they just put in a bigger motor, fiddled a bit with the turbo, etc.
But then came Toyota: they changed the old ways of just throwing more horsepower onto the old engine and did things differently, smarter, more innovative, better suited with the (evolved) technology at hand. Result: a smaller engine, less fuel consumption, and outperforming the Volkswagen on all fronts.

So here we are: throwing expensive hardware (CPU Memory) onto our servers, fiddling with caching and tuning in the margin.

And don't get me wrong: when your target audience is the brochure site, then that is perfectly fine: why spend a lot of time (and risk) in doing things differently when you can just add more horse power to your server.

When looking back at how these large sites started and what they have grown into now, I think other decisions should have been made, other platforms should have been build upon. But with the years of investment in these sites, that is not a decision made lightly.

So my approach currently is to try to improve (albeit the Volkswagen way) so Joomla is better suited for these large databases, or at least have a discussion on this so that we can conclude that Joomla is indeed not suited for these large sites, or it is and work together on it. I currently have the performance of that one query down from 60 seconds to 5 seconds, can you imagine what the impact of this is from an ecological and sustainability point of view?

Looking at the traction here I strongly think it is not suited and there is no interest in this.

But that is okay, frustrating but okay, as that is also what Open Source and volunteering means. In the end everybody decides if they have an issue and if they want to solve it. When there is no issue then there is nothing to solve. Plain and simple.

So if you also feel the pain: let's work on this together and come up with a solution that is hard to resist! Or at least with a solution that can be implemented 'on top of' Joomla so no time and effort goes to waste.

[sergeytolkachyov]

So if you also feel the pain: let's work on this together and come up with a solution that is hard to resist! Or at least with a solution that can be implemented 'on top of' Joomla so no time and effort goes to waste.

I want to say a huge thank you for the research done and the solutions found. For my part, I am ready to offer help in testing various scenarios of Joomla behavior with such a volume of articles. It's a pain. Some of my clients have similar problems, but with custom fields the question has already arisen: "Maybe not Joomla?.." .

I described your search in a short post in the Russian Joomla community and on some IT resources. I really hope that someone else will respond and offer all possible help. I also hope that the Joomla maintainers will find time to pay attention to this issue, and if a PR solution is made, it will be accepted into the Joomla core. This would be a wonderful event that would expand the boundaries of Joomla application.

OPen Source is somewhat like a jungle. No one owes anyone anything here. These improvements may not be made quickly, but they shall be done. At least partially.

[b2z]

Up. Lets support!