i'm afraid no indexes can help on a query with LIKE and OR
anyway how many articles do you have ? 20K ?

@alikon @wilsonge @SharkyKZ @richard67 @brianteeman @astridx @C-Lodder @Quy @zero24 @zero-24

On a news page with 10 categories such as sports, finance, technology, etc., and with 10 subcategories per category such as soccer, baseball, tennis, etc., adding an article by subcategory and also multilanguage, it is not an exaggeration or crazy to arrive soon to quantities of more than 20,000 items, since the items are also saved and not deleted. Joomla is an excellent and extraordinary CMS for its modular system, user management, flexibility and diversity compared to any other CMS, giving it tremendous power and potential, but one of its few adversities against other CMS such as Drupal or Wp is that since its origin Joomla has a big problem in the structure of the way he handles large volumes of articles and the same thing happens with menus; Neither Drupal nor WP have problems with this handling of large volumes of Articles, specifically the structure of the assets kills all the benefits and benefits of Joomla over other CMS. If it is possible to eliminate the bottleneck of the assets in Joomla, it would no longer have a rival as the best CMS and the same with the bottleneck of large volumes of menus and submenus.

Could a fulltext index be used here?

iirc FULLTEXT index can be used starting mysql ≥ 5.6

True but would it help j4+?

@HLeithner @alikon @wilsonge @SharkyKZ @richard67 @brianteeman @astridx @C-Lodder @Quy @zero24 @zero-24

Possibly there is ignorance of me in relation to the existence or structure of the Assets, but it definitely has an error in the structure that goes beyond creating indexes, since for example imagine that you have 100,000 word documents on your computer and that for create, change, or delete a word file the computer had to make a comparison with the 100,000 documents ... because that makes joomla with the Assets, it's crazy! It only makes sense for very specific cases in which it is required to do a search for a specific comparative such as finding repeated files and even then joomla does it wrong because it does not define author or any date but does so with 100% of the files that are much worse because what it does with the Assets is also with the plugins, themes, modules etc. since the Assets are also all of them, it is as if you were going to create a word document and the non-only computer will search in the 100,000 Word documents but also include all excel, powerpoint, etc, etc, etc, perhaps only excluding images. It is definitely an error of logic, structure and design.

@Stuartemk did you come up with a solution for Joomla for this?

@Stuartemk for note: spamming everyone will not help to solve the issue at all, even opposite.

Just showing "slow logs" does not helps much, it may be due to bad server performance.
Would be much useful if you tell more detail and be more specific, eg: in which cases you have a slow down: when add/edit content, or when doing something else.

All this water about windows and files will no one read.

I join cause I also have the same issue, a slow query on assets table with a com_content of more than 30K articles, any workaround sofar?
That query is executed every time a com_content page is loaded in frontend, with or without cache, and as far as I can see, that query is executed even if I load the page as a visitor or guest

small update: the assets query takes 22ms in joomla debug but if I run the same query in phpmyadmin it's blazing fast

@Notebit 22ms would actually not be slow in my opinion. In any case, here is a PR which should reduce the number of rows in that table, improving performance especially for large sites like these: #39165
Please test that PR and see if this helps with your issues.