Slowness at the "user & roles" page

[jonasraoni]

Describe the bug
A journal with around 20K users is taking around 60s to retrieve a response for the User & Roles interface.

The origin is MySQL trashing the disk with temporary tables (our max_heap_table_size is defined with 128MB, which is a reasonably high value) for the query, which can't be perfectly optimized.

What application are you using?
OJS/stable-3_3_0

Additional information

PRs

• stable-3_3_0
• Feature/stable 3 3 0/8700 improve slow query #8702
• Feature/stable 3 3 0/8700 improve slow query ojs#3799
• Feature/stable 3 3 0/8700 improve slow query omp#1386
• Feature/stable 3 3 0/8700 improve slow query ops#510
• stable-3_4_0
• Feature stable 3 4 0 8700 improve slow query #9994
• Feature/stable 3 4 0/8700 improve slow query ojs#4286
• Feature/stable 3 4 0/8700 improve slow query omp#1572
• Feature/stable 3 4 0/8700 improve slow query ops#686
• main
• Feature main 8700 improve slow query #9997
• Feature/main/8700 improve slow query ojs#3870
• Feature/main/8700 improve slow query omp#1387
• Feature/main/8700 improve slow query ops#511

[asmecher]

@jonasraoni, are you going to work on this yourself? If so, great; if not, this isn't enough to be able to reproduce and correct the issue.

[jonasraoni]

Okay, I've assigned myself.

I did a generic fix on the DAO.inc.php which brought the time to 8s, and another specific one which decreased it to 2s.

I'll create a PR and check if the Laravel framework does a similar optimization out-of-the-box.

[jonasraoni]

@asmecher Here are the PRs, I've left some notes at the PR.
Meanwhile I'll check if the issue happens at the Laravel framework, if yes, at least the generic patch will probably be much simpler to apply.

• Feature/stable 3 3 0/8700 improve slow query #8702
• Feature/stable 3 3 0/8700 improve slow query ojs#3799

[jonasraoni]

Closing this one, I fixed a couple of unrelated bugs and converted the affected queries to the Laravel Query.