This pull request (PR) changes the list query of the articles model in backend so that when workflows are enabled, articles where the workflow association is missing are not hidden but still shown in the list.

What about when workflows is disabled? Are articles where the association is missing still hidden?

What about when workflows is disabled? Are articles where the association is missing still hidden?

No, they are shown also in that case.

What about when workflows is disabled? Are articles where the association is missing still hidden?

No, they are shown also in that case.

thats a great improvement - thanks

@brianteeman I'm pretty sure we have an issue for the missing articles without workflow association in the list, but I don't find it right now. If you remember it or can find it, please let me know here so I can refer to it in this PR. Thanks in advance.

I don't think there is an open issue. It was always stated as expected and intended behaviour

Maybe related PR #40176?

Maybe related PR #40176?

@QuyTon Yes, related, but not the same.

Works good. Before the PR it takes around 1s for the list query and 0.5s after the PR on test site with ~44k of articles.
And new filter option also works.

I have tested this item ✅ successfully on b89c945

Works good. Before the PR it takes around 1s for the list query and 0.5s after the PR on test site with ~44k of articles. And new filter option also works.

@Fedik I think when filtering for a particular workflow stage, the performance gain might not be that big, but when not filtering for a workflow stage or filtering for "- None -" with the new option it is big.

trying to test this but each time I use the plugin it responds with There is an error in a sample data plugin. Response is invalid.

had to disable field creation

trying to test this but each time I use the plugin it responds with There is an error in a sample data plugin. Response is invalid.

had to disable field creation

@brianteeman You mean @Fedik 's big data plugin?

You can't have broken article associations in the database and overcome this problem by just having them optional, when workflow is enabled.
Solution is:

• move the wf related joins + selects to a $workflow_enabled-if (but let them inner joins)
• Show somewhere if you have broken/missing associations (DB checker?)
• Add a "Fix associations" button somewhere.

First point gives the "performance boost", 2+3 helps keep the data clean.

First point gives the "performance boost"

No, it only would be an improvement when workflow is switched off, while this PR here also improves performance when workflow is switched on. The inner joins seem to be the problem.

In addition it would result in different behaviour when workflow is switched on or off. When switched on, the inner joins would result in articles with missing workflow associations being shown and being counted in the total rows at the bottom right corner of the list, and with workflow on they would not be shown and not be counted in the total rows.

Anyway, if there are better ways to fix it then go for it.

Anyway it seems this PR here is not a complete solution, as we can see the articles with missing workflow association in the list, but selecting them and using batch processing to assign them a workflow stage seems not to work.

trying to test this but each time I use the plugin it responds with There is an error in a sample data plugin. Response is invalid.
had to disable field creation

@brianteeman If you mean @Fedik 's big data plugin: Does it help to disable the "Content - Smart Search" and "Behaviour - Versionable" plugins?

Switching back to draft mode as I'm not sure if I will follow this approach.

I think the PR is good for what it does.

@SmikeSix2 Could you check if PR #45660 fixes your issue? If yes, please report back there. Thanks in advance.

hi yes this should improve it a bit.
we basically moved to our own fork of joomla, since we have like 20 portals (with millions of articles spread across).
I spend the last year upgrading from 3 and improve performance in j4 and just hacked into it, so I cant test it anymore.

From what I did and still can remember, it affected backend /administrator and frontend. So I just did everything to get performance ignoring any software standards. (we are frontend and backend now at 40ms / 200ms in uncached, before partly 1-5s+, monthly like 50m+ page views, but lots of caches in-between):

All fixes here are just try & error independent from how joomla was designed and may only work for our portal as we use it:
This means using maria db:

Frontend:

1. Same as here in the user ArticleModel.php (flipped to LEFT joins)

But i believe it also needs an index (Point 2)

Joomla also does a count * with the same query for the pagination. So I added a info parameter to the query class ->

Also I badly hacked: Joomla\Database\DatabaseQuery
$query->info ="articles_com_content";

and do a clear of the slow joins here:

In: MVC/Model/BaseDatabaseModel

 protected function _getListCount($query)
          if($query->info == "articles_com_content"){
                $query->clear('select')->clear('order')->clear('limit')->clear('offset')->clear('join', '#__categories')->select('COUNT(*)', true);
            }else{
                $query->clear('select')->clear('order')->clear('limit')->clear('offset')->select('COUNT(*)');
            }

Other SQL queries:

2. I think it also needed those indexes to work

**CREATE INDEX idx_access_state_catid ON hycwr_content (access, state, catid);

CREATE INDEX idx_content_filter_dates ON yrfu6_content (access, state, catid, publish_up, publish_down);

ALTER TABLE hycwr_content ADD INDEX idx_state_publish_up_catid (state, publish_up, catid);**

3. For the backend editors. The list were also really slow.

There it needed this in administrator..../ArticlesModel.php
->join('LEFT', $db->quoteName('#__workflow_associations', 'wa') . ' FORCE INDEX (PRIMARY)', $db->quoteName('wa.item_id') . ' = ' . $db->quoteName('a.id'))

4. The overview sides where you just had a list of articles grouped were also extremely slow, this needed a workaround for the created & publish_up. Basically telling the DB to use a generated view so the queries later can join faster:

ALTER TABLE `yrfu6_content` ADD COLUMN `effective_publish_date` DATETIME AS (CASE WHEN `publish_up` IS NULL THEN `created` ELSE `publish_up` END) PERSISTENT;


ALTER TABLE `yrfu6_content` ADD INDEX `idx_eff_pub_date_created` (`effective_publish_date` DESC, `created` DESC);

Issue here is that the Article-Editor then assumes this field exists so you cant save Articles. I changed that behavior too but I'm unsure where I put it. I basically force this Sort for all our content then. So the parameter sort doesn't work anymore, but for us that was fine:

also user ArticleModel.php:

        $query->order(
            "a.`effective_publish_date` DESC"
           // $db->escape($this->getState('list.ordering', 'a.ordering')) . ' ' . $db->escape($this->getState('list.direction', 'ASC'))
        );

If someone is interested I can just send the changed source files for someone to take a look

ALTER TABLE yrfu6_content ADD COLUMN effective_publish_date DATETIME AS (CASE WHEN publish_up IS NULL THEN created ELSE publish_up END) PERSISTENT;

We can't do that as the database checker does not understand calculated columns, and it would not be easy to make it understand as the expression stored in the information schema can differ from the one in the CREATE TABLE statement and is also different between MySQL and MariaDB.

You can't have broken article associations in the database and overcome this problem by just having them optional, when workflow is enabled. Solution is:

* move the wf related  joins + selects to a `$workflow_enabled`-if (but let them inner joins)

* Show somewhere if you have broken/missing associations (DB checker?)

* Add a "Fix associations" button somewhere.

First point gives the "performance boost", 2+3 helps keep the data clean.

That is bad data design. For ensuring data integrity in the database, inner joins are no solution. They can only ensure data integrity of the selected data but not of data in the database.

If having workflow data is mandatory for articles, then the usage of an xref table like #__workflow_associations is not the solution. You can use an extra column like stage_id in the #__content table itself and can require content tables on extensions having such a column if they shall support workflows, and using a NOT NULL constraint on that column would make sure it is set. Ideally you would define a foreign key on that column. This would be the right way to ensure data integrity of in the database. The xref table could then be used in addition for backreferences from workflows to content, but it would not really be necessary anymore.

The inner joins hide articles with missing workflow data, but the articles are still there, you just can not do anything with them.

About the other suggestion to wrap the workflow joins into an if condition so they are only used when workflow is enabled I am not sure. It would mean a fundamental change of functionality. The workflow column is hidden in the articles list view in backend when workflows are disabled, but you could still use the list query to get that data to see what the workflow stage would be for that article if workflows were enabled. To me it seems to be done like this by purpose. But if we can do that, it would of course increase performance when workflow is disabled.

Anyway, I changed this PR here to draft because I think it might need a bit more work so you can not only delete those articles but also assign them to a workflow stage using the batch processing or the edit form.

We can collect opinions here and discuss that in the maintainers team, too, and if at the end a decision is made that this PR is not accepted then I can live with that.

I've made this PR because I have seen that other attempts to fix the performance issues are not really feasible.

MySQL has deprecated the FORCE INDEX and similar clauses in favour of optimizer hints, and they might drop them in future versions. MariaDB does not go that way. So it would need different syntax for these databases in future.

Another attempt which I've tried was to use a subquery (or more precise, an instant view) for the workflow tables. The performance gain was dramatic. But on a standard installation of MySQL8 it caused the "max_join_size exceeded" error when having huge data (some 30k articles) because MySQL abuses the max_join_size for other purposes in order to avoid slow queries in general (and is often wrong with that).