joomla backend get slow with a lot of menu items

[benshaty]

Hi,
I have a joomla site with ~3000+ menu items.

Steps to reproduce the issue

when i get into the template manager / module manager
the SQL query takes ~40 sec.

ifound that the SQL has GROUP BY command that take ~10 sec. to process.
if i remove it and add "DISTINCT" after the select the query take ~0.05 sec.

sql query that take 10 sec.

SELECT a.id AS value, a.title AS text, a.alias, a.level, a.menutype, a.type, a.template_style_id, a.checked_out
FROM XXXXXXX_menu AS a
LEFT JOIN XXXXXXX_menu AS b
ON a.lft > b.lft
AND a.rgt < b.rgt
WHERE a.published != -2
GROUP BY a.id, a.title, a.alias, a.level, a.menutype, a.type, a.template_style_id, a.checked_out, a.lft
ORDER BY a.lft ASC

fixed sql

SELECT DISTINCT a.id AS value, a.title AS text, a.alias, a.level, a.menutype, a.type, a.template_style_id, a.checked_out
FROM XXXXXXX_menu AS a
LEFT JOIN XXXXXXX_menu AS b
ON a.lft > b.lft
AND a.rgt < b.rgt
WHERE a.published != -2

ORDER BY a.lft ASC

Is it possible to fix this SQL?

[brianteeman]

Is it possible for you to provide a database dump of just the #__menu table? This will really help people to test without having to create a gazillion menu items

[brianteeman]

Reset priority to documented standards https://docs.joomla.org/Priority

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.}

[ggppdk]

GROUP BY should be used when there is need to calculate an aggregate value via an aggregate function such as COUNT(), MAX() etc,

in this case i do not see any aggregate function inside the SQL query

• database server should treat this as DISTINCT, but as i have seen in my own code, MySQL does not do it (maybe newer versions do it?)

now if the PHP code

• does not have an if statement to add an aggregate function (someone should check the PHP code)
• and since as we can see that SELECT clause is same as GROUP BY clause , the GROUP BY should be replaced with DISTINCT giving a big performance difference

[benshaty]

brianteeman - i sent you the menu dump in an Email.
thanks in advance

[brianteeman]

Got it thanks

[brianteeman]

Thanks for sending the data
The first thing you may need to do is to increase the limit change max_input_vars in php.ini.
It will need to be at least 50 higher then the total number of menu items

[benshaty]

didn`t work :(

[brianteeman]

No that won't make it faster but it will prevent errors due to the large
number of variables that are loaded
On 27 Mar 2016 2:23 pm, "Ben Shaty" notifications@github.com wrote:

didn`t work :(

—
You are receiving this because you commented.
Reply to this email directly or view it on GitHub
#9516 (comment)

[benshaty]

hi brianteeman,
did you find something?

[brianteeman]

@alikon do you think you will be able to take a look at this - I can confirm the slow queries and I have the db dump if it will help

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.}

[alikon]

I will look at it but i need more clear info on how to replicate.... Can you help me ...

[brianteeman]

I will email you a database dump with 3000+ menu items You will need to ensure that you have php_max_vars set to greater than the number of menu items or joomla will fail Then to see the issue try to open any module for editing and you will see the delay. This is due to the way a module can be assigned to a menu item and in this case we have 3000+

[benshaty]

hi,
about the Joomla! Issue Tracker - CMS.
can you change the category from mssql to mysql?
i dont know how and i think its relevent.
thanks :)

On Thu, Mar 31, 2016 at 12:29 PM, Brian Teeman notifications@github.com
wrote:

I will email you a database dump with 3000+ menu items

You will need to ensure that you have php_max_vars set to greater than the
number of menu items or joomla will fail

Then to see the issue try to open any module for editing and you will see
the delay. This is due to the way a module can be assigned to a menu item
and in this case we have 3000+

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub
#9516 (comment)

[roland-d]

As per your request I changed the category. Since there is no MySQL category, I set it to SQL.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.}

[benshaty]

thanks :)

[zero-24]

This can be closed as we have a PR by @alikon here: #9689

[brianteeman]

Closed as we have a PR for testing #9689

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.}

[brianteeman]

@benshaty please can you test the changes made in #9689 - I had amazing results with it with your data set

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.}

[benshaty]

Thanks. 
I'll check it on Sunday and update.  

נשלח מסמארטפון ה-Samsung Galaxy שלי.

-------- הודעה מקורית --------

מאת: Brian Teeman notifications@github.com

תאריך:01/04/2016 11:40 (GMT+02:00)

אל: joomla/joomla-cms joomla-cms@noreply.github.com

עותק: Ben Shaty benshaty@gmail.com

נושא: Re: [joomla/joomla-cms] joomla backend get slow with a lot of menu items (#9516)

@benshaty please can you test the changes made in #9689 - I had amazing results with it with your data set

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9516.
—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub