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/main/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 #9982
• 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 #8898
• 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]

@asmecher The getCountForPagination() fits my expectations, and it's a natural replacement to my workaround. I've updated the PRs, if you'd like to double check, it's ready. If everything was ok last time, I can proceed with the merge, the only extra addition was replacing offset(0)->limit(0)->getCount() by getCount() (I didn't do it on stable-3_3_0 though).

ps: Only Travis was able to build my PRs for main, I might take a look at the GitHub actions later (it might not be understanding the long branch)