Hello Andy,

I just tried again, I can confirm the problem comes after push the “Add Members” button as mentioned in Post #1. I just manually killed the query after about 2 minutes on the shell, here is mysqladmin processlist info:

1773787 | ********* | localhost                 | *****     | Query   | 135  | Copying to tmp table         | SELECT SQL_CALC_FOUND_ROWS wp_sfmemberships.user_id, wp_sfmembers.display_name, wp_sfmembers.admin

 

I don’t remember about a setting for a single group by user, is that enabled by default ? where to change it ?

In the past, before that issue happened, I actually setup many users in more than one group and they are still active.

Thank you.

It seems to me that the important issue here is why is a perfectly reasonable and good query blowing out your system. While my test rig might only have 7-8000 users in it this query runs for me consistently between 30 and 40 milliseconds.

Can you explain to me why this query is creating s temporary table in the output you posted above? and I suppose I should ask is your database in need of optimisation?

Well, the SQL output copied here is truncated by mysqladmin processlist listing, could you please confirm the query that may ran slow here is that one:

SELECT SQL_CALC_FOUND_ROWS wp_sfmemberships.user_id, wp_sfmembers.display_name, wp_sfmembers.admin

FROM wp_sfmemberships
RIGHT JOIN wp_sfmembers ON wp_sfmembers.user_id = wp_sfmemberships.user_id
WHERE (

usergroup_id !=6
AND admin =0

)
OR (

wp_sfmemberships.user_id IS NULL 
AND admin =0

)
ORDER BY display_name
LIMIT 0 , 100

Explain on this query gives the following:

Thank you.

Yes indeed – that’s the query.