[3.9] Menu structure is not optimal for large sites only to micro sites

[Stuartemk]

Steps to reproduce the issue

In this example the site has 691 menus in total, between menus and submenus. Doing the query in MySql of slow log returns the following result.

root@myserver ~ # mysqldumpslow -s c -t 1 /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 14973 Time=0.01s (16s) Lock=0.01s (0s) Rows=16.0 (238391), root[root]@localhost SELECT id FROM mydb_menu WHERE link LIKE 'S' AND menutype !='S' AND type='S'

Expected result

It should be able to handle a high volume of menus with high speed, say 10 level 1 menus with 10 level 2 submenus and 50 level 3 submenus

Actual result

The response time is extremely slow even though it is a dedicated server with 64GB RAM and 1TB NVMe.

This is a great bottleneck, among others that Joomla has. But for now this report is focused on this great bug.

System information (as much as possible)

MySql 8 Php 7.4 Joomla 3.9.20 64GB Ram 1TB NVMe

Additional comments

[richard67]

@Stuartemk Does it speed up if you define a few nex indexes as follows?

ALTER TABLE `#__menu` ADD INDEX `idx_link` (`link(100)`);
ALTER TABLE `#__menu` ADD INDEX `idx_menutype` (`menutype`);
ALTER TABLE `#__menu` ADD INDEX `idx_type` (`type`);

(replacing the #__ by your actual db prefix, e.g. 'mydb_'.)

Of course you should prefer to test this on a testing site, i.e. a copy of the real site, but if you can't avoid to test on the real site, don't forget to drop these indexes after the test.

[Stuartemk]

@Stuartemk Does it speed up if you define a few nex indexes as follows?

ALTER TABLE `#__menu` ADD INDEX `idx_link` (`link(100)`);
ALTER TABLE `#__menu` ADD INDEX `idx_menutype` (`menutype`);
ALTER TABLE `#__menu` ADD INDEX `idx_type` (`type`);

(replacing the #__ by your actual db prefix, e.g. 'mydb_'.)

Of course you should prefer to test this on a testing site, i.e. a copy of the real site, but if you can't avoid to test on the real site, don't forget to drop these indexes after the test.

Thank you very much for the reply @richard67 . Right now I'm going to try it.

[richard67]

@Stuartemk Is just an experiment. Not sure if we will add these indexes to the core. If you have other columns in your WHEREclause, too, it might need also indexes for those. Not sure if we can cover every possible scenario. But the experiment will help to see if indexes can help. I don't have such large data here to do such tests, so thanks in advance if you can do it.

[Stuartemk]

ALTER TABLE mydb_menu ADD INDEX idx_link (link(100)); ALTER TABLE mydb_menu ADD INDEX idx_menutype (menutype); ALTER TABLE mydb_menu ADD INDEX idx_type (type);

---

Error SQL query: Copy

ALTER TABLE mydb_menu ADD INDEXidx_link (link (100)) MySQL has said: Documentation

1072 - Key column 'link (100)' does not exist in table

---

@richard67

[richard67]

ah, my mistake, typo, it should be:

ALTER TABLE `#__menu` ADD INDEX `idx_link` (`link`(100));

and not

ALTER TABLE `#__menu` ADD INDEX `idx_link` (`link(100)`);

The other 2 statements should be ok.

[Stuartemk]

Error SQL query: Copy

ALTER TABLE mydb_menu ADD INDEXidx_link (link (100)) MySQL has said: Documentation

1067 - Invalid default value for 'checked_out_time'

@richard67

[richard67]

@Stuartemk That's a side effect of strict mode not allowing the old zero dates. You can bypass this by having following line at the top of your SQL statements and execute it before the other statements: SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

I.e. have following in your SQL commands window e.g. in phpMyAdmin:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
ALTER TABLE `#__menu` ADD INDEX `idx_link` (`link`(100));

and run the complete sequence of commands.

[Stuartemk]

Thank you so much

Excuse me my ignorance, the changes would have to be seen immediately or is it necessary to let some time go by, say 24hrs?

@richard67

[richard67]

It may need some time until MySQL has (re)built the indexes, but I don't think that lasts 24 hours. No idea though. Try it out.

[Stuartemk]

indexing does not solve the problem, I would like to have more programming knowledge to be able to solve this and contribute to jommla, I cannot affirm it but I have the feeling that the biggest problem is that the query in the database, all together the menus join them with the languages ​​and with the templates, so if there are 1000 menus but it is a multisite, let's say with 6 languages ​​they are already 6000 in total and if there is a template per language in total, the query ends up being 36000!

It is the same very old and already known problem of JOOMLA with the assets, which in the case that it is, say, a news portal, when it starts and has few articles it goes very fast but over time if it reaches 50,000 news and if It is multisite with 6 languages, there are a total of 300,000 and JOOMLA gathers all those articles in the assets together with the ASSETS of the plugins, the ASSETS of the modules, with the ASSETS of the components, with the ASSETS of the templates, etc. So in the end the query in the database is terribly inefficient. The structure of the ASSETS and the Menus is an absolute error, and more so being a multisite, when the multisite function was introduced in JOOMLA, nobody raised the big problem that it would cause. The error is as serious as if to open a WORD or EXCEL file in office, when opening office, the program would first consult all the files on the PC and if the PC had about 500,000 EXCEL files, then the OFFICE program It would take hours to open a single EXCEL file, THIS IS HOW SERIOUS THE PROBLEM IS AND THE STRUCTURE OF THE ASSETS AND MENUS IS TERRIBLE. What JOOMLA should do, in the case of menus, is only to do the query by language first, that at least in the example above would reduce the query instead of 36,000 to 5,000. And in the case of Assets, you should completely separate the ASSETS of the articles completely independent of the JOOMLA system ASSESTS (Components, Modules, Pugins and Templates). Although I would propose to completely remove ALL ASSETS. I would not like to make the comparison, please forgive me, but for example WP (WordPress) does not use an asset system or something similar, and in that sense it is much faster than JOOMLA and that is why it is widely used for news portals As I said, I would not like to make that tedious comparison, however it is important both to learn from other CMS and to see what the competition does best.

Thank you.

@richard67 @wilsonge @alikon @infograf768 @Quy @Hackwar @N6REJ @brianteeman @SharkyKZ @PhilETaylor @C-Lodder @astridx @tassosm @zero-24 @dgrammatiko @HLeithner @ciar4n @rdeutz @bembelimen @roland-d @chmst @Fedik @SniperSister @laoneo @mbabker @twister65 @Bakual

[laoneo]

Removing assets from Joomla completely would also eliminate a selling point of it which distinguishes us from others. But I see your problem with assets/permissions per article. In DPCalendar I'v removed permissions per event and kept only per calendar (which are categories in the background) as performance became very quickly an issue. Perhaps this is something we can consider for Joomla as well to remove permissions per article as I'm pretty sure that in most of the cases permissions per category would be enough.

[SharkyKZ]

https://github.com/joomla/joomla-cms/pull/14268 was supposed to improve asset performance.

[brianteeman]

Perhaps this is something we can consider for Joomla as well to remove permissions per article as I'm pretty sure that in most of the cases permissions per category would be enough.

@laoneo For the site I am currently working on that you have seen it would be a problem and I would have to create multiple categories to resolve it eg News (for guests) News (for members) News (for gold members)

[Fedik]

In this example the site has 691 menus in total,

how this is possible, you are robot? :smiley:

[alikon]

@Stuartemk can you tell me where/when this query is issued ? SELECT id FROM mydb_menu WHERE link LIKE 'S' AND menutype !='S' AND type='S'

[Stuartemk]

@Stuartemk can you tell me where/when this query is issued ? SELECT id FROM mydb_menu WHERE link LIKE 'S' AND menutype !='S' AND type='S'

Hi @alikon

root@myserver ~ # mysqldumpslow -s c -t 1 /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 14973 Time=0.01s (16s) Lock=0.01s (0s) Rows=16.0 (238391), root[root]@localhost SELECT id FROM mydb_menu WHERE link LIKE 'S' AND menutype !='S' AND type='S'

I don't know if it's a good idea, but I think that this menu structure is very old, which is the same since before the MULTI LANGUAGE feature was introduced in joomla, so I deduce that at least it would have to be added: Select 'language' or 'template_style_id'

since in phpmyadmin it seems to me that 'template_style_id' corresponds to the language.

I also think that a Select 'parent_id' should be added too

And although I'm not sure that doing all this is the right thing to do, I would like you to tell me which file I have to modify to add the above so that I can do tests and see if it improves the speed

I think it should be something like

SELECT id FROM mydb_menu WHERE link LIKE 'S' AND menutype !='S' AND type='S' AND language='S' AND parent_id='S'

Please tell me which file I have to modify to add the above

Thank you

@richard67 @laoneo @SharkyKZ @brianteeman @Fedik

[brianteeman]

since in phpmyadmin it seems to me that 'template_style_id' corresponds to the language.

That would only be if your site is using a different template style for each language and therefore unique to your site configuration. Its not a feature of a multilanguage config

[Stuartemk]

Analyzing in depth, since the ASSETS were included in Joomla, a big mistake was committed by containing all the ASSETS together, perhaps it was a poor planning or by the ancient technology of that moment; The best thing would be that there will be an assets table for menus, a table of assets for components, a table of assets for plugins, a table of assets for modules, a table of assets for templates, a table of assets for languages, a table Since it would be more efficient, fast, safe and structured. In the previous examples I exposed as an example that to read a data, let's say menu at a large site with 5000 items the database has to read everything, when the efficient would be to read the assets of menus that will surely be less or much smaller. You have to take advantage of the opportunity before launching Joomla 4.0 to do things well. Possibly it would be appropriate to embed a number or identification class (ID) to know when it is a menu type asset, component, module, plugin, language, etc. That way it would be safer. I am sure that this change would be greatly beneficial in every way, speed, security, structure, update, etc. Otherwise, all the assets are currently together is as if instead there was a folder for menus, components, modules, templates, plugins, languages, etc ... all files were together together in the same folder. Finally, it would possibly also be very useful to identify the core assets with the purpose of in case of disaster the system remains operant without risk ... Something as well as with the Core plugins that is restricted its uninstallation.

Thank you

@richard67 @laoneo @SharkyKZ @brianteeman @Fedik @wilsonge