# Mysql Template views are too slow with mutiple joins and union query **URL:** \ **Category:** Uncategorized\ **Tags:** mysql, percona\ **Created:** [February 28, 2024, 12:04pm UTC](https://forums.percona.com/t/mysql-template-views-are-too-slow-with-mutiple-joins-and-union-query/28796 "2024-02-28T12:04:04Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Dev\_1\_Einpix](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dev_1_einpix/32/15360_2.png) [@Dev\_1\_Einpix](https://forums.percona.com/u/Dev_1_Einpix)\ **Post date:** [February 28, 2024, 12:04pm UTC](https://forums.percona.com/t/mysql-template-views-are-too-slow-with-mutiple-joins-and-union-query/28796/1 "2024-02-28T12:04:04Z")
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](https://easyupload.io/rx6zjc) [Result\_15.pdf](https://forums.percona.com/uploads/short-url/4KytHjUmOW1F9M7aTY0fsckEvLR.pdf) (33.5 KB) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [February 29, 2024, 10:47pm UTC](https://forums.percona.com/t/mysql-template-views-are-too-slow-with-mutiple-joins-and-union-query/28796/2 "2024-02-29T22:47:39Z")
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. ---
**Author:** ![Dev\_1\_Einpix](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dev_1_einpix/32/15360_2.png) [@Dev\_1\_Einpix](https://forums.percona.com/u/Dev_1_Einpix)\ **Post date:** [March 4, 2024, 4:42am UTC](https://forums.percona.com/t/mysql-template-views-are-too-slow-with-mutiple-joins-and-union-query/28796/3 "2024-03-04T04:42:13Z")
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? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [March 4, 2024, 4:16pm UTC](https://forums.percona.com/t/mysql-template-views-are-too-slow-with-mutiple-joins-and-union-query/28796/4 "2024-03-04T16:16:47Z")
> [@Dev\_1\_Einpix](#): > > 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? 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.