How to reuse a huge query in MySQL

Question

I have very huge query which takes data from many tables and group them by all non-calculated columns. And I need to reuse this query many times but with other grouping and filtering. In MSSQL I use CTE or temporary tables for such purpose but MySQL doesn't support CTE and I can't refer temporary table more than one time in the same query (this is sad and illogical limitation of MySQL).

SELECT 
    t1.VideoId, 
    t1.RegionId, 
    t1.CountryId, 
    t1.PerCountryCount,
    t2.PerRegionCount
FROM (
    SELECT 
        VideoId, 
        RegionId, 
        CountryId, 
        SUM(PlayCount) PerCountryCount
    FROM TrackedData
    GROUP BY VideoId, RegionId, CountryId
) t1
INNER JOIN (
    SELECT 
        VideoId, 
        RegionId, 
        SUM(PlayCount) PerRegionCount
    FROM TrackedData
    GROUP BY VideoId, RegionId
) t2

This example is simplified but shows the problem. The t1 query has more detailed data and I want to reuse it in t2 query since in real project it is too hard to take data in t1 query (million rows, many filters and groupings, etc.). the second reason why I want to reuse this query is query length. I don't want repeat just a bit changed query which has around 60 rows of code.

Answer 1

You can reuse queries using the concept of views:

https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html

Simple example, in practice big_ass_select_query probably has a bunch of joins

DROP VIEW IF EXISTS big_ass_select_query;

CREATE VIEW big_ass_select_query AS
SELECT col1 from table1;

Use it as a normal query

SELECT * FROM big_ass_select_query WHERE col1 > 1;