When trying to access the user admin page on a system with lots of users (1M+) it takes a long time (over one minute) to return the page.

CommentFileSizeAuthor
#2 user.admin-D6.patch1.56 KBJeremyFrench

Comments

moshe weitzman’s picture

moshe weitzman commented

This is pretty awful. See below.

SELECT DISTINCT u.uid, u.name, u.status, u.created, u.access FROM users u LEFT JOIN users_roles ur ON u.uid = ur.uid WHERE u.uid != 0 ORDER BY u.created DESC LIMIT 0, 50

And the explain

id  select_type table type  possible_keys key key_len ref rows  Extra 
1 SIMPLE  u ALL PRIMARY NULL  NULL  NULL  3362990 Using where; Using temporary; Using filesort
1 SIMPLE  ur  ref PRIMARY PRIMARY 4 magic.u.uid 77841 Using index; Distinct
JeremyFrench’s picture

JeremyFrench commented
StatusFileSize
new user.admin-D6.patch1.56 KB

When there are no filters the join in this query is not used but takes a lot of time to execute, it also necessitates the distinct clause. All that is really needed in these cases is:

SELECT u.uid, u.name, u.status, u.created, u.access FROM users u WHERE u.uid != 0 ORDER BY u.created DESC LIMIT 0, 50

Running these queries locally (with about 1M users) in mysql results in the execute time reducing from 60s to 0.01s

A patch is applied which examines the filters returned from user_build_filter_query() to decide whether to put the join in the query or not.

JeremyFrench’s picture

JeremyFrench commented
Issue tags: +Performance
JeremyFrench’s picture

JeremyFrench commented
Status: Active » Needs review
JeremyFrench’s picture

JeremyFrench commented
Assigned: JeremyFrench » Unassigned
moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

Nice solution.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Version: 6.x-dev » 7.x-dev
Status: Reviewed & tested by the community » Patch (to be ported)

Bugs are first fixed in the latest Drupal version, in this case Drupal 7 and then backported. Otherwise, this same issue will come back in Drupal 7, and we need to attack it again. Thanks!

moshe weitzman’s picture

moshe weitzman commented
Priority: Normal » Critical
JeremyFrench’s picture

JeremyFrench commented
Assigned: Unassigned » JeremyFrench

I'm working on a patch, However I think that http://drupal.org/node/604304 is the D7 version of this bug?

JeremyFrench’s picture

JeremyFrench commented
Assigned: JeremyFrench » Unassigned
Status: Patch (to be ported) » Active

The D7 version of this bug is now fixed. See http://drupal.org/node/604304, should the reviewed D6 patch above be put into that ticket, or can we set this ticket as D6?

moshe weitzman’s picture

moshe weitzman commented
Version: 7.x-dev » 6.x-dev
Status: Active » Reviewed & tested by the community

Lets try to just work here. Back to RTBC

bjcool’s picture

bjcool commented

subscribe

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Reviewed & tested by the community » Needs work

Looks like a good patch, helps us avoid some joins in the initial state. The D7 patch also looses the DISTINCT saying that we can only choose one role, so we'll not get duplicates. That is true here as well, is it?

JeremyFrench’s picture

JeremyFrench commented
Status: Needs work » Needs review

The D7 patch also looses the DISTINCT saying that we can only choose one role, so we'll not get duplicates. That is true here as well, is it?

Not quite. The D7 patch is a little more sophisticated. With that there is a join for each filter, so there is no need for a DISTINCT. However the way that the query is built up in D6 makes this impractical. So for D6 we still need the distinct.

moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

back to rtbc

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Status: Reviewed & tested by the community » Fixed

Ok, thank you. Committed to Drupal 6.

Status: Fixed » Closed (fixed)
Issue tags: -Performance

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags: +Performance

Restoring issue tags, see #2125755: System messages removed all issue tags during D7 upgrade.