Mysql Template views are too slow with mutiple joins and union query

,
1

Hello,
We have recently migrated from mysql Ver 15.1 Distrib 10.5.19-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 to Percona MySQL XtraDB cluster with mysql 5.6. The query to join with the template view is taking too much time (approx 56 seconds).

Query:

SELECT t1.id, t1.reporter_id FROM tasks t1
UNION
SELECT ta.task_id, ta.user_id FROM task_assignees ta
UNION
SELECT t3.id, t3.responsible_id FROM tasks t3 WHERE t3.responsible_id IS NOT NULL
UNION
SELECT tw.task_id, tw.user_id FROM task_watchers tw
UNION
SELECT t4.id, u4.id FROM tasks t4
JOIN venues v4 ON v4.id = t4.venue_id
JOIN partnership p4 on v4.id = p4.venue_id AND p4.status = ‘active’
JOIN partnership_observers po4 on p4.id = po4.partnership_id
JOIN users u4 ON po4.user_id = u4.id
WHERE p4.guest_organization_id = u4.organization_id
AND t4.reporter_organization_id = u4.organization_id
UNION
SELECT t5.id, u5.id FROM tasks t5
JOIN venues v5 ON v5.id = t5.venue_id
JOIN partnership p5 on v5.id = p5.venue_id AND p5.status = ‘active’
JOIN partnership_observers po5 on p5.id = po5.partnership_id
JOIN users u5 ON po5.user_id = u5.id
WHERE p5.guest_organization_id = u5.organization_id
AND t5.assignee_organization_id = u5.organization_id
UNION
SELECT t6.id, u6.id FROM tasks t6
JOIN venues v6 ON v6.id = t6.venue_id
JOIN partnership p6 on v6.id = p6.venue_id AND p6.status = ‘active’
JOIN partnership_observers po6 on p6.id = po6.partnership_id
JOIN users u6 ON po6.user_id = u6.id
WHERE p6.guest_organization_id = u6.organization_id
AND t6.owner_organization_id = u6.organization_id
UNION
SELECT t71.id, u71.id FROM tasks t71
JOIN organizations o71 on t71.owner_organization_id = o71.id
JOIN users u71 ON u71.organization_id = o71.id
JOIN venues v71 ON v71.id = t71.venue_id AND v71.organization_id = u71.organization_id
WHERE JSON_CONTAINS(u71.roles, ‘“ROLE_TASK_ADMIN”’) = 1
AND u71.is_bound_to_venues = 0
UNION
SELECT t72.id, u72.id FROM tasks t72
JOIN organizations o72 on t72.owner_organization_id = o72.id
JOIN users u72 ON u72.organization_id = o72.id
JOIN venues v72 ON v72.id = t72.venue_id
JOIN user_bound_venue ubv72 ON u72.id = ubv72.user_id AND ubv72.venue_id = v72.id
WHERE JSON_CONTAINS(u72.roles, ‘“ROLE_TASK_ADMIN”’) = 1
AND u72.is_bound_to_venues = 1
UNION
SELECT t8.id, u8.id FROM tasks t8
JOIN venues v8 on t8.venue_id = v8.id
JOIN user_user_group uug8 ON uug8.user_id = t8.reporter_id
JOIN user_visible_groups uvg8 on uug8.user_group_id = uvg8.user_group_id
JOIN users u8 ON uvg8.user_id = u8.id
JOIN user_bound_venue ubv8 ON u8.id = ubv8.user_id
WHERE u8.can_see_tasks_for_groups = 1
AND u8.is_bound_to_venues = 1
AND ubv8.venue_id = t8.venue_id
UNION
SELECT t9.id, u9.id FROM tasks t9
JOIN user_user_group uug9 ON uug9.user_id = t9.reporter_id
JOIN user_visible_groups uvg9 on uug9.user_group_id = uvg9.user_group_id
JOIN users u9 ON uvg9.user_id = u9.id
JOIN venues v9 ON t9.venue_id = v9.id
WHERE u9.can_see_tasks_for_groups = 1
AND u9.is_bound_to_venues = 0
AND v9.organization_id = u9.organization_id
UNION
SELECT t10.id, u10.id FROM tasks t10
JOIN task_assignees ta10 on t10.id = ta10.task_id
JOIN user_user_group uug10 ON uug10.user_id = ta10.user_id
JOIN user_visible_groups uvg10 on uug10.user_group_id = uvg10.user_group_id
JOIN users u10 ON uvg10.user_id = u10.id
JOIN user_bound_venue ubv10 ON u10.id = ubv10.user_id
WHERE u10.can_see_tasks_for_groups = 1
AND u10.is_bound_to_venues = 1
AND ubv10.venue_id = t10.venue_id
UNION
SELECT t11.id, u11.id FROM tasks t11
JOIN task_assignees ta11 on t11.id = ta11.task_id
JOIN user_user_group uug11 ON uug11.user_id = ta11.user_id
JOIN user_visible_groups uvg11 on uug11.user_group_id = uvg11.user_group_id
JOIN users u11 ON uvg11.user_id = u11.id
JOIN venues v11 ON t11.venue_id = v11.id
WHERE u11.can_see_tasks_for_groups = 1
AND u11.is_bound_to_venues = 0
AND v11.organization_id = u11.organization_id
UNION
SELECT t12.id, u12.id FROM tasks t12
JOIN user_user_group uug12 ON uug12.user_id = t12.responsible_id
JOIN user_visible_groups uvg12 on uug12.user_group_id = uvg12.user_group_id
JOIN users u12 ON uvg12.user_id = u12.id
JOIN user_bound_venue ubv12 ON u12.id = ubv12.user_id
WHERE u12.can_see_tasks_for_groups = 1
AND u12.is_bound_to_venues = 1
AND ubv12.venue_id = t12.venue_id
UNION
SELECT t13.id, u13.id FROM tasks t13
JOIN user_user_group uug13 ON uug13.user_id = t13.responsible_id
JOIN user_visible_groups uvg13 on uug13.user_group_id = uvg13.user_group_id
JOIN users u13 ON uvg13.user_id = u13.id
JOIN venues v13 ON t13.venue_id = v13.id
WHERE u13.can_see_tasks_for_groups = 1
AND u13.is_bound_to_venues = 0
AND v13.organization_id = u13.organization_id;

EXPLAIN statement: Upload Files | Free File Upload and Transfer Up To 10 GB
Result_15.pdf (33.5 KB)

2

Hello @Dev_1_Einpix,
5.6 has been dead MySQL for almost 8 years. Please upgrade to MySQL 8 before examining any query issues. 5.7 had many improvements to query executions and so did MySQL 8. It would be a waste of effort to diagnose something that cannot be fixed in 5.6.

For the record, it would be better for you to run each of these UNIONs as independent queries and merge them together in your code. That will absolutely result in faster execution because MySQL can then execute them in parallel, vs the UNION which must execute each part one at a time in a single thread.

3

Thanks @matthewb for your reply.

Initially, we have installed MySQL 8 but it does not have query_cache option available. We thought that issue was due to the query_cache, but we were wrong.

We can not run query one by one because, I’ve to use that query in JOIN. So, can you please suggest the best solution?

4

You need to use the result of this giant-multi-union query in a JOIN with another query? Honestly, you should re-think this design. Rule #1 of query optimization is don’t be afraid/unwilling to rewrite the query to achieve better performance. There are only so many knobs/dials you can turn in MySQL. Sometimes the only solution is to redesign the query/process.

Idea: create a temporary table. Break apart theses UNIONs into simple queries that can be ran in parallel. Each query stores the results into the temp table. Now you can join this temp table with your other query.