[#9516] - joomla backend get slow with a lot of menu items
- Closed
- 31 Mar 2016
- Medium
- Build: master
- # 9516
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?
| Priority | Critical | ⇒ | Medium |
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.
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
| Status | New | ⇒ | Information Required |
brianteeman - i sent you the menu dump in an Email.
thanks in advance
Got it thanks
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
| Status | Information Required | ⇒ | Confirmed |
didn`t work :(
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)
hi brianteeman,
did you find something?
@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.
I will look at it but i need more clear info on how to replicate.... Can you help me ...
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+
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 failThen 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)
| Category | IIS MS SQL | ⇒ | IIS SQL |
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.
thanks :)
| Status | Confirmed | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-31 21:24:32 |
| Closed_By | ⇒ | 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.
@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.
Thanks.
I'll check it on Sunday and update.
נשלח מסמארטפון ה-Samsung Galaxy שלי.
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
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