optimization - MySQL Query with Sub-Query optimisation -

optimization - MySQL Query with Sub-Query optimisation -

i have built cms scheme typical user/group/permission scheme users can members of groups, , permissions can applied either user directly, or groups users can members of.

permissions can 'wildcard' (e.g. apply objects) or apply specific objects designated module name , row id. permissions can 'allow' grants access, or 'deny' prevents access , overrides 'allow' permissions have been granted elsewhere. deny stored in userpermission/grouppermission table creating row 'allow' column set 0.

the next query used (and works) list users have been granted specific 'wildcard' permission (permissionid 123).

select `user`.* ( select `user`.*, `userpermission`.`allow` `user_allow`, `userpermission`.`permissionid` `user_permissionid`, `grouppermission`.`allow` `group_allow`, `grouppermission`.`permissionid` `group_permissionid` `user` left bring together `userpermission` on `user`.`userid` = `userpermission`.`userid` , `userpermission`.`module` = '*' , `userpermission`.`rowid` = '*' , `userpermission`.`permissionid` = 18 left bring together `usergroup` on `user`.`userid` = `usergroup`.`userid` left bring together `grouppermission` on `usergroup`.`groupid` = `grouppermission`.`groupid` , `grouppermission`.`module` = '*' , `grouppermission`.`rowid` = '*' , `grouppermission`.`permissionid` = 18 ( `grouppermission`.`allow` = 1 or `userpermission`.`allow` = 1 ) ) `user` left bring together `userpermission` on `user`.`userid` = `userpermission`.`userid` , `userpermission`.`permissionid` = `user`.`user_permissionid` , `userpermission`.`allow` = 0 , `userpermission`.`module` = '*' , `userpermission`.`rowid` = '*' left bring together `usergroup` on `user`.`userid` = `usergroup`.`userid` left bring together `grouppermission` on `usergroup`.`groupid` = `grouppermission`.`groupid` , `grouppermission`.`permissionid` = `user`.`group_permissionid` , `grouppermission`.`allow` = 0 , `grouppermission`.`module` = '*' , `grouppermission`.`rowid` = '*' grouping `user`.`userid` having count(`userpermission`.`userpermissionid`) + count(`grouppermission`.`grouppermissionid`) = 0

however slow (~0.5 seconds, ~3000 users, ~250 groups, ~10000 usergroup joins, ~30 permissions, ~150 grouppermissions , ~30 userpermissions).

permissionid per illustration above 1 permision. may necessary check multiple permissions e.g. in(18,19,20) instead of = 18

explain provides next output - think i've got right columns indexed i'm not sure how (or if possible) index derived table:

+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+ | 1 | primary | [derived2] | | null | null | null | null | 62 | using temporary; using filesort | | 1 | primary | userpermission | ref | userid,permissionid,allow | userid | 4 | user.userid | 2 | | | 1 | primary | usergroup | ref | userid | userid | 4 | user.userid | 4 | | | 1 | primary | grouppermission | ref | groupid,permissionid,allow | permissionid | 4 | user.group_permissionid | 3 | | | 2 | derived | user | | null | null | null | null | 2985 | | | 2 | derived | userpermission | ref | userid,permissionid | permissionid | 4 | | 1 | | | 2 | derived | usergroup | ref | userid | userid | 4 | [database].user.userid | 4 | | | 2 | derived | grouppermission | ref | groupid,permissionid | permissionid | 4 | | 3 | using | +----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+

is possible re-write query without sub-query can optimised, or optimise as-is?

if info construction needs changing isn't huge issue.

you're left joining , afterwards count non-null rows , check if there exist none. hope realize wording, more complicated needs be. can rewrite whole query this:

select `user`.* `user` left bring together `userpermission` on `user`.`userid` = `userpermission`.`userid` , `userpermission`.`module` = '*' , `userpermission`.`rowid` = '*' , `userpermission`.`permissionid` = 18 left bring together `usergroup` on `user`.`userid` = `usergroup`.`userid` left bring together `grouppermission` on `usergroup`.`groupid` = `grouppermission`.`groupid` , `grouppermission`.`module` = '*' , `grouppermission`.`rowid` = '*' , `grouppermission`.`permissionid` = 18 (`grouppermission`.`allow` = 1 or `userpermission`.`allow` = 1) , not exists (select 1 `userpermission` `user`.`userid` = `userpermission`.`userid` , `userpermission`.`allow` = 0 , `userpermission`.`module` = '*' , `userpermission`.`rowid` = '*' , `userpermission`.`permissionid` = 18 ) , not exists (select 1 `grouppermission` `usergroup`.`groupid` = `grouppermission`.`groupid` , `grouppermission`.`allow` = 0 , `grouppermission`.`module` = '*' , `grouppermission`.`rowid` = '*' , `grouppermission`.`permissionid` = 18 );

what's great exists(), is, stops entry found. don't have rows , check afterwards if there none.

another way write query this:

select `user`.* `user` left bring together `userpermission` on `user`.`userid` = `userpermission`.`userid` , `userpermission`.`module` = '*' , `userpermission`.`rowid` = '*' , `userpermission`.`permissionid` = 18 left bring together `usergroup` on `user`.`userid` = `usergroup`.`userid` left bring together `grouppermission` on `usergroup`.`groupid` = `grouppermission`.`groupid` , `grouppermission`.`module` = '*' , `grouppermission`.`rowid` = '*' , `grouppermission`.`permissionid` = 18 left bring together `userpermission` on `user`.`userid` = `up`.`userid` , up.`allow` = 0 , up.`module` = '*' , up.`rowid` = '*' , up.`permissionid` = 18 left bring together grouppermission gp on `usergroup`.`groupid` = `gp`.`groupid` , gp.`allow` = 0 , gp.`module` = '*' , gp.`rowid` = '*' , gp.`permissionid` = 18 (`grouppermission`.`allow` = 1 or `userpermission`.`allow` = 1) , up.userid null , gp.groupid null;

have seek 1 works improve you.

mysql optimization