Support Forum
Support Forum
Offline
Yes indeed I rewrote the query and the routines involved which took the load off of the query and mySQL and my tests and profiling suggest it is now perfectly efficient. Maybe your issue is more to do with the AJAX functionality surrounding the add members form. Are you seeing any reported issues in the script console when you run it?
 |
YELLOW
SWORDFISH
|
Offline
I believe the problem is still DB related as I got a long running query after listing the members from a user group.
As this locks the whole forum, I did not leave the query blocking for long time and killed it after a minute or so.
Offline
How are you determining you have a 'long running query' please? What are you using?
|
YELLOW
SWORDFISH
|
Offline
Well, queries even the most complex ones in SP seem to execute in less than a second on my server.
In the case of this problem here, I just do mysqladmin -p processlist from the shell and watch the value in the time column grow for the line of the query I believe is related to the User group action.
Offline
So - just lets confirm things. The action we are talking about here in the initial query that runs in the user group management panels when you use the 'Add Members' tool. Is that right?
And if so - can we also assume your site is set up to allow a user to be in more than a single user group?
|
YELLOW
SWORDFISH
|
Offline
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.
Offline
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?
|
YELLOW
SWORDFISH
|
Offline
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:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | wp_sfmembers | ref | admin_idx | admin_idx | 2 | const | 99698 | Using temporary; Using filesort |
| 1 | SIMPLE | wp_sfmemberships | ALL | NULL | NULL | NULL | NULL | 99740 | Using where |
Thank you.
All RSS1 Guest(s)