View Issue Details

IDProjectCategoryView StatusLast Update
05441User patchesOther issuespublic2012-06-21 13:23
Reporteruser15490Assigned Toc_schmitz  
PrioritynormalSeverityminor 
Status closedResolutionfixed 
Product Version1.71+ 
Target VersionFixed in Version2.00 
Summary05441: getuserlist() "List users from same group as me + all my childs" scalability blocker [mysql]
Description

In getuserlist(), the query which will "List users from same group as me + all my childs" involves multiple full table scans and temporary table generation. With a few thousand users and a few thousand groups, this query will consume 100% of a cpu core for 5-30 minutes, depending on what's in cache.

==========

existing query:

$uquery = " SELECT * FROM ".db_table_name('users')." where uid in
(SELECT u.uid FROM ".db_table_name('users')." AS u,
".db_table_name('user_in_groups')." AS ga ,".db_table_name('user_in_groups')." AS gb
WHERE u.uid=$myuid
OR (ga.ugid=gb.ugid AND ( (gb.uid=$myuid AND u.uid=ga.uid) OR (u.parent_id=$myuid) ) )
GROUP BY u.uid)";

proposed replacement query:

SELECT from users where uid in (
SELECT uid from user_in_groups where ugid in (
SELECT ugid from user_in_groups where uid=xxx
)
)
UNION
SELECT
from users where users.parent_id=xxx

======

old query runtime

mysql> SELECT u.* FROM users AS u,
-> user_in_groups AS ga ,user_in_groups AS gb
-> WHERE u.uid=675
-> OR (ga.ugid=gb.ugid AND ( (gb.uid=675 AND u.uid=ga.uid) OR
-> (u.parent_id=675) ) )
-> GROUP BY u.uid, u.users_name, u.password, u.full_name,
-> u.parent_id, u.lang,
-> u.email, u.create_survey, u.create_user, u.delete_user, u.superadmin,
-> u.configurator, u.manage_template, u.manage_label, u.htmleditormode;

4 rows in set (30 min 28.57 sec)

==============

new query runtime (cold cache)

mysql> SELECT from users where uid in (
-> SELECT uid from user_in_groups where ugid in (
-> SELECT ugid from user_in_groups where uid=675
-> )
-> )
-> UNION
-> SELECT
from users where users.parent_id=675
-> ;

4 rows in set (10.19 sec)

new query runtime (warm cache)

4 rows in set (0.00 sec)

TagsNo tags attached.
Complete LimeSurvey version number (& build)10912

Activities

user15490

2011-09-01 22:13

 

getuserlist_mysql_performance.diff (1,369 bytes)
Index: common_functions.php
===================================================================
--- common_functions.php	(revision 10912)
+++ common_functions.php	(working copy)
@@ -1423,12 +1423,13 @@
             // List users from same group as me + all my childs
             // a subselect is used here because MSSQL does not like to group by text
             // also Postgres does like this one better
-            $uquery = " SELECT * FROM ".db_table_name('users')." where uid in
-                        (SELECT u.uid FROM ".db_table_name('users')." AS u,
-                        ".db_table_name('user_in_groups')." AS ga ,".db_table_name('user_in_groups')." AS gb
-                        WHERE u.uid=$myuid
-                        OR (ga.ugid=gb.ugid AND ( (gb.uid=$myuid AND u.uid=ga.uid) OR (u.parent_id=$myuid) ) )
-                        GROUP BY u.uid)";
+            $uquery = " SELECT * from ".db_table_name('users')." where uid in (
+                          SELECT uid from ".db_table_name('user_in_groups')." where ugid in (
+                            SELECT ugid from ".db_table_name('user_in_groups')." where uid=$myuid
+                          )
+                        )
+                        UNION
+                        SELECT * from ".db_table_name('users')." where users.parent_id=$myuid";
         }
         else
         {
c_schmitz

c_schmitz

2011-09-18 12:40

administrator   ~16292

Awesome - thank you for this nice patch!

Related Changesets

LimeSurvey: Yii d3e3f2c9

2011-09-18 03:41:16

c_schmitz

Details Diff
Fixed issue 05441: getuserlist() "List users from same group as me + all my childs" scalability issue - patch by gkuchta

git-svn-id: file:///Users/Shitiz/Downloads/lssvn/source/limesurvey_ci@11006 b72ed6b6-b9f8-46b5-92b4-906544132732
Affected Issues
05441
mod - application/helpers/common_helper.php Diff File

Issue History

Date Modified Username Field Change
2011-09-01 22:08 user15490 New Issue
2011-09-01 22:13 user15490 File Added: getuserlist_mysql_performance.diff
2011-09-18 12:37 c_schmitz Assigned To => c_schmitz
2011-09-18 12:37 c_schmitz Status new => assigned
2011-09-18 12:40 c_schmitz Note Added: 16292
2011-09-18 12:40 c_schmitz Status assigned => resolved
2011-09-18 12:40 c_schmitz Fixed in Version => 2.00
2011-09-18 12:40 c_schmitz Resolution open => fixed
2012-03-14 21:08 c_schmitz Changeset attached => Import 2012-03-09 13:30:34 Yii d3e3f2c9
2012-06-21 13:23 c_schmitz Status resolved => closed