@benshaty Please test #8863 perhaps that solves your issue.

it`s not that.
there is that query that i posted that uses:
->select('a.id AS value,
instead we changed it to:
select('DISTINCT(a.id) AS value,

and then the system started to work perfectly.
you can check this ( #9516) and see all the problem.
thanks in advance

@benshaty
i am confused by the title of this thread,

• you say "menus" in the title and then in your description you say about "modules"
• in the title you say "slow" and in the text you say "query ERROR", but there is not error

(ok i think everybody understands that you mean performance)

Now which exactly view do you mean ?

• the modules listing in the modules manager

That the query can not remove group by

• because it has some aggregate calculation that calculates the number of assigned pages, which is also a sortable column
• to optimized it and remove group by we would need to remove the "Pages" column ... or make it non-sortable and then break the query into 2 queries

There is another query that might be worth optmizing (may give some benefits in various views) and which can be optimized:

@alikon (i am only referencing you here, because your PRs are more respected than mine)

you may want to look into 1 more case
of unneeded GROUP-BY clause that can be replaced with DISTINCT:

• no aggregate function is used
• the group by clause is the same as select

https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_modules/helpers/modules.php#L189-L195

in my site i have more then 3000~ menu items.
and more then 1000~ modules.
in march i had the same performance issue.
the SQL query that sort the menu items in "Menu Assignment" was fixed with this code:
->select('DISTINCT(a.id) AS value,
and then all the template and menus workd fast. (#9689)
now i have the same issue with the modules manager.

Hi, news about this issue?
Think I got the same problem. 10000~ menu items per language, 1000~ modules per language. Why is that query on menu table running before showing modules overview in backend modules manager? What information is taken from menu table that isn't availible from modules table? Is it just for search tools / select page option?

Just to clarify: Error message is because i killed the query after a while at mysql workbench.

@benshaty
@prathumwan

Please test #11627

The effect that you see is due to the creation of search filter "Select page"

• which in this case retrieves all menu items

Similar performance issue will have pages that retrieve ALL menu items using the menus helper class

Ok will test #11627

I tried to figure out why the effects of this query are so extreme. I changed slightly this and that and found that the join to the language table is the cause of the performance drop.

The change from LEFT JOIN to a simple JOIN reduced the duration for about 10000 menu items from 70 seconds to 0.35 seconds! Ok, so far so good (I thought). The simple JOIN of course did not deliver the records which had an no corresponding language field in the menu table. I added the missing records by hand to language table, and the result is amazing: LEFT JOIN and simple JOIN deliver the identical result now, but the LEFT JOIN is still at the same slow speed!

I don't know what reason cause this behavior, but good to know.

so you have not tested #11627 yet ?

about

The change from LEFT JOIN to a simple JOIN reduced the duration for about 10000 menu items from 70 seconds to 0.35 seconds

if you (incorrectly) filter out many records (in some way) then of course you will less records to participate in the query and get performance improvement, that LEFT JOIN needs to be LEFT

please test #11627

ok great, just finished.
#11627 works well, can't see any difference.

The speed is back. Amazingly if you switch of the language plugin the speed is back also. There must be a MySQL issue with this double left join query usage.

@benshaty

Please test PR #11627

and also see comment (about which backend screens get a benefit):
#11627 (comment)

Also this PR for menu item edit form
#11628

can we close this sicne we have two PR?

Closed as requested