Mysql – Convert UNION query to JOIN. Is it actually needed for high performance

join;MySQLperformancequery-performanceunion

I have the following table structure in MySql.

enter image description here

To get the list of roles for a particular user, I use the following union Query,

SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_USER AS ru ON r.id = ru.ROLE_ID
WHERE ru.user_id ="100"
UNION SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.id
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.user_id ="100"
LIMIT 0 , 30

Explain of the above query,
enter image description here

and to get the list of permissions for a user I wrote the following query,

SELECT p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_USER AS ru ON ru.ROLE_ID = r.ID
WHERE ru.USER_ID ="100"
UNION
SELECT  p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.ID
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.USER_ID ="100"

Explain results of the above query is,

enter image description here

Is it possible to convert the above two queries to use JOIN instead of UNION ? If can why? Will there be any issues on huge data with this query. I see that all the data are indexed properly.

Best Answer

You use LIMIT without ORDER BY - that may return different 30 rows each time it is executed.

Other than that if you combine the two unioned queries into one join, you will have OR in WHERE which will check columns from different tables - that is probably going to be much slower than what you have now, union is good for this. You might want to use UNION ALL instead if you know that there will be no duplicates or that they are no problem to you as it does not have to check and eliminate them and can be even faster that UNION.

Related Question