Multi-table multi-table join query, and optimization-CodePudding

Multi-table multi-table join query, optimization for sure, thank you!

 

SELECT rbac_powers.*,modules.name AS modulename FROM ( SELECT rbac_powers_id FROM ( SELECT rbac_userpowers.rbac_powers_id FROM rbac_userpowers WHERE user_id='5b286d5e3d868' UNION SELECT rbac_grouppowers.rbac_powers_id FROM rbac_usergroup LEFT JOIN rbac_grouppowers ON rbac_grouppowers.rbac_group_id=rbac_usergroup.rbac_group_id WHERE rbac_usergroup.user_id='5b286d5e3d868' UNION SELECT rbac_rolepowers.rbac_powers_id FROM rbac_usergroup LEFT JOIN rbac_grouprole ON rbac_grouprole.rbac_group_id=rbac_usergroup.rbac_group_id LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id=rbac_grouprole.rbac_role_id WHERE rbac_usergroup.user_id='5b286d5e3d868' UNION SELECT rbac_rolepowers.rbac_powers_id FROM rbac_userrole LEFT JOIN rbac_rolepowers ON rbac_rolepowers.rbac_role_id=rbac_userrole.rbac_role_id WHERE rbac_userrole.user_id='5b286d5e3d868' ) B WHERE B.rbac_powers_id !=' 'GROUP BY rbac_powers_id) A LEFT JOIN rbac_powers ON rbac_powers. Id=A.r bac_powers_id LEFT JOIN modules ON modules. Id=rbac_powers. Modules_id WHERE modules. Foldername=' base 'AND ((rbac_powers. Name=' base 'AND rbac_powers. Level=1) OR (rbac_powers. Name=' index 'AND rbac_powers. Level=2) OR (rbac_powers. Name=' index 'AND rbac_powers. Level=3) OR (rbac_powers. Name=' test1 'AND rbac_powers. Level=4) OR (rbac_powers. Name='? Id=awdw 'AND rbac_powers. Level=5) ORDER BY rbac_powers. Level ASC

CodePudding user response:

Good, can't see the execution plan, estimate and help ability is limited

CodePudding user response:

To help you beautify the, the other overwhelmed;

 SELECT rbac_powers. *, modules. The name AS modulename 
The FROM (
The SELECT rbac_powers_id 
The FROM (
The SELECT rbac_userpowers. Rbac_powers_id 
The FROM rbac_userpowers 
WHERE user_id='5 b286d5e3d868' 
The UNION 
The SELECT rbac_grouppowers. Rbac_powers_id 
The FROM rbac_usergroup 
LEFT the JOIN rbac_grouppowers ON rbac_grouppowers. Rbac_group_id=rbac_usergroup. Rbac_group_id 
WHERE rbac_usergroup. User_id='5 b286d5e3d868' 
The UNION 
The SELECT rbac_rolepowers. Rbac_powers_id 
The FROM rbac_usergroup 
LEFT the JOIN rbac_grouprole ON rbac_grouprole. Rbac_group_id=rbac_usergroup. Rbac_group_id 
LEFT the JOIN rbac_rolepowers ON rbac_rolepowers. Rbac_role_id=rbac_grouprole. Rbac_role_id 
WHERE rbac_usergroup. User_id='5 b286d5e3d868' 
The UNION 
The SELECT rbac_rolepowers. Rbac_powers_id 
The FROM rbac_userrole 
LEFT the JOIN rbac_rolepowers ON rbac_rolepowers. Rbac_role_id=rbac_userrole. Rbac_role_id 
WHERE rbac_userrole. User_id='5 b286d5e3d868' 
B) 
WHERE B.r bac_powers_id! 
=' 'GROUP BY rbac_powers_id 
) A 
LEFT the JOIN rbac_powers ON rbac_powers. Id=A.r bac_powers_id 
LEFT the JOIN modules ON modules. Id=rbac_powers. Modules_id 
WHERE modules. Foldername='base' 
AND ((rbac_powers. Name='base' 
AND rbac_powers. Level=1) 
OR (rbac_powers. Name='index' 
AND rbac_powers. Level=2) 
OR (rbac_powers. Name='index' 
AND rbac_powers. Level=3) 
OR (rbac_powers. Name='test1' 
AND rbac_powers. Level=4) 
OR (rbac_powers. Name='? Id=awdw '
AND rbac_powers. Level=5)) 
The ORDER BY rbac_powers. Level ASC

CodePudding user response:

You this is to let people optimize SQL query? How to optimize it? The MySQL engine will help you optimize
Your database table structure, the amount of each table data, index all don't know, how to optimize?

CodePudding user response:

Can the statements can shorten and take into effect, the execution efficiency now is 0.0016 100000 quantity of the data

CodePudding user response:

List the amount of data, index, table structure, engines, the primary key these all have no, how to optimize?

CodePudding user response:

SELECT
Rbac_powers. *, modules. The NAME AS modulename - here you will get all fields rbac_powers. * the * best into a specified field below so
The FROM
(
SELECT
Rbac_powers_id
The FROM
(
SELECT
Rbac_userpowers. Rbac_powers_id
The FROM
Rbac_userpowers
WHERE
User_id='5 b286d5e3d868'
The UNION
SELECT
Rbac_grouppowers. Rbac_powers_id
The FROM
Rbac_usergroup
LEFT the JOIN rbac_grouppowers ON rbac_grouppowers. Rbac_group_id=rbac_usergroup. Rbac_group_id
WHERE
Rbac_usergroup. User_id='5 b286d5e3d868'
The UNION
SELECT
Rbac_rolepowers. Rbac_powers_id
The FROM
Rbac_usergroup
LEFT the JOIN rbac_grouprole ON rbac_grouprole. Rbac_group_id=rbac_usergroup. Rbac_group_id
LEFT the JOIN rbac_rolepowers ON rbac_rolepowers. Rbac_role_id=rbac_grouprole. Rbac_role_id
WHERE
Rbac_usergroup. User_id='5 b286d5e3d868'
The UNION
SELECT
Rbac_rolepowers. Rbac_powers_id
The FROM
Rbac_userrole
LEFT the JOIN rbac_rolepowers ON rbac_rolepowers. Rbac_role_id=rbac_userrole. Rbac_role_id
WHERE
Rbac_userrole. User_id='5 b286d5e3d868'
B)
WHERE B.r bac_powers_id!=' '-- here if you confirm rbac_powers. Id this is not null, here can get rid of the following first left the join can be changed to inner join
) A
LEFT the JOIN (
Select * from rbac_powers where rbac_powers. NAME='base' AND rbac_powers. LEVEL=1
The UNION
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull