The fulltext column had once been added with PR #28384 .

The fulltext column had once been added with PR #28384 .

I see, but not only the backend, the frontend is slow too.

I just unpacked your backup on my localhost. I know it's not a web server with limitations, but I just wanted to share my results:
Homepage: 225ms average loading time
Back-end articles page with 20 items: 1.10s average loading time

Is it possible that anything else on your configuration may be slowing it down?

I found your test component, so I ran it to add a few more articles. I let it add just over 50 articles. Interestingly enough, the homepage loading time went up to ~2.5s. I deleted them, but the homepage is still slow now.

With a sufficiently powerful PC, for example, with a Core i5 12th generation, M.2 SSD, and 16GB of RAM, the performance will be satisfactory after several refreshes (F5). This implies that you would need to invest in a VPS costing at least $40 per month to achieve relatively smooth performance with 20,000 articles. However, I don't consider this to be an ideal solution.

And this is the result on my live site, hawkhost vps 8Gb ram, 4 core (8GB LAX package).

What happens if you make a backup of your live site and try on localhost? Is the loading time the same?
What I'm trying to figure out here is why did it load well for me when I downloaded your backup and why doesn't it now.

In my case, the three queries that take up most of the loading time are three consecutive queries to the #__content with a lot of columns on BaseDatabaseModel.php:164. They take 900ms, 770ms and 770ms.

What happens if you make a backup of your live site and try on localhost? Is the loading time the same? What I'm trying to figure out here is why did it load well for me when I downloaded your backup and why doesn't it now.

This might because mysql cache your query result on ram. But sometimes it does, sometimes not.
And at my local, the backend article list gets even more time to load, it takes 8s.

In my case, the three queries that take up most of the loading time are three consecutive queries to the #__content with a lot of columns on BaseDatabaseModel.php:164. They take 900ms, 770ms and 770ms.

You can comment out these code on file administrator\components\com_content\src\Model\ArticlesModel.php to get better speed.

This might because mysql cache your query result on ram. But sometimes it does, sometimes not. And at my local, the backend article list gets even more time to load, it takes 8s to load.

Yup, that was it. After restarting MySQL, I have a homepage with 225ms loading time.

I added some articles using your test component, again. Homepage > 2.5s. Restart MySQL server > 225ms.

You can comment out these code on file administrator\components\com_content\src\Model\ArticlesModel.php to get better speed.

Commenting the lines you mentioned drops the back-end loading time from 1.25s to 450ms and does not seem to affect the functionality otherwise.

The content: search functionality still works, and a simple search for introtext and fulltext in the back-end reveals no further uses of these columns in the list view, as far as I can tell. I would suggest you make a PR so that others can check as well.

@mavrosxristoforos those code was added since 4.0. I think we have to check carefully before making any PR

8d436f8
cf9f0fd

That is correct.
I'm thinking now that despite the fact that the articles listing back-end doesn't use these two columns directly in the list view, any extension that loads articles using the ArticlesModel will probably require them.