Associated table query more slowly, statements and great god can have a look at how to optimize the code is as follows:

SELECT
U.U SER_ID,
U.U SER_TYPE,
GROUP_CONCAT (DISTINCT R.R OLE_NAME) AS ROLE_NAME,
GROUP_CONCAT (DISTINCT DG. DATA_GROUP_NAME) AS DATA_GROUP_NAME,
GROUP_CONCAT (DISTINCT RG. RES_GROUP_NAME) AS RES_GROUP_NAME,
GROUP_CONCAT (DISTINCT SD DEPT_NAME) AS DEPT_NAME,
U.U SERNAME,
U.U SER_LOGIN_NAME,
U.E MAIL
The FROM
SYS_USER U
LEFT the JOIN SYS_USER_ROLE UR ON UR. USER_ID=U.U SER_ID
ON LEFT JOIN SYS_ROLE R R.R OLE_ID=UR. ROLE_ID
LEFT the JOIN SYS_USER_DATA_GROUP UDG ON UDG. USER_ID=U.U SER_ID
LEFT the JOIN SYS_DATA_GROUP DG ON DG. DATA_GROUP_ID=UDG. DATA_GROUP_ID
LEFT the JOIN SYS_USER_RES_GROUP URG ON URG. USER_ID=U.U SER_ID
LEFT the JOIN SYS_RESOURCE_GROUP RG ON RG. RES_GROUP_ID=URG. RES_GROUP_ID
LEFT the JOIN SYS_USER_DEPT UD ON UD. USER_ID=U.U SER_ID
LEFT the JOIN SYS_DEPT SD ON SD. DEPT_ID=UD. DEPT_ID

AND the USERNAME like concat (' % ', # {sysUser. USERNAME}, '%')

AND USER_LOGIN_NAME like concat (' % ', # {sysUser. UserLoginName}, '%')



GROUP BY
U.U SER_ID

CodePudding user response:

Just look at the code, so actually optimization space is not particularly big, and a lot of fields, there is no corresponding table alias, but also increase the difficulty of the optimization,
Look at this, it should be the statistics of user information, suggest adding redundant table directly, the content, you need all in, then somewhere, to maintain this table alone,

CodePudding user response:

reference 1st floor AHUA1001 response:

just look at the code, so actually optimization space is not particularly big, and a lot of fields, there is no corresponding table alias, but also increase the difficulty of the optimization,
Look at this, it should be the statistics of user information, suggest adding redundant table directly, the content, you need all in, then somewhere, separate the table maintenance,

Thanks for bosses reply, this is the query the user table information query at the same time the user's role name (role table SYS_ROLE) affiliation (SYS_DEPT), such as a total of four tables, each table and the middle of the users table, there is a table (SYS_USER_ROLE, SYS_USER_DEPT), now I can only optimize in the statement, the database I not currently operating risks, so I ask, what other ways can optimize the statement

CodePudding user response:

Optimizing space with no good, you like and have left # % index don't have to use the seek, but if your SYS_USER table fields you can build a more united index (USER_ID, USER_TYPE, USERNAME, USER_LOGIN_NAME, EMAIL)

Built the joint index has two benefits
1, you this query sys_user table can go index scan and cover does not need to back to the table index can
2, group by USER_ID with this index, so no extra sort operations,

In addition you all left the join table associated field must must be indexed,

CodePudding user response:

reference chengangcsdn reply: 3/f

optimize space with no good, you like and have left # % index don't have to use the seek, but if your SYS_USER table fields you can build a more united index (USER_ID, USER_TYPE, USERNAME, USER_LOGIN_NAME, EMAIL)

Built the joint index has two benefits
1, you this query sys_user table can go index scan and cover does not need to back to the table index can
2, group by USER_ID with this index, so no extra sort operations,

All your other left the join table associated field must must be indexed,

Thank the bosses of reply, give me a big help