How to change the request to speed up the work?

Question

MySQL

Mysql(innodb), structure:
1) Subject - general information about the subject, name, date of creation, etc. (about 15 fields in total - varchar, int, datetime, int key is auto-incrementing)
2) Item indicators in the system 1(80 fields - all double, except for the key Int) connection with the first connection according to the field key 1k1.
3) Indicators of the subject in the system 2 (60 fields - all double, except for the key Int) connection with the first connection according to the field key 1k1.
4) Indicators of the subject in the system 3 (600 fields - all double, except for the key Int) connection with the first connection according to the field key 1k1.
5) Student table - 1000 records 5 text fields and Key 1 to many relationship.
6) Table table_name - 1000 records 5 text fields and Key 1 to many relationship.
7) Table room - 1000 records 5 text fields and Key 1 to many relationship.
Each table has 70,000 records, except for tables 5-7 there are up to 1000 records.
Depending on the user's requirements, an sql query is generated, in which several queries will be combined through UNION, since there are many conditions for different combinations of indicators. The query can contain both the mat of the operation between the fields of the tables, and the results of CASE WHEN. The number of fields for select is unlimited, in the condition - the same. There can be up to 100 such associations.

For example:

select t1.id `id`, concat(table_name1.name, ' - ', table_name2.name) `Material Name` ,st.Name "Student", r.name Room, concat(i.param1, ' - ', i.param2) `Param 1-2`, 
concat(COALESCE(s3.param1,'0'), ' - ', COALESCE(s3.param2, '0')) `Test Params`,DATE_FORMAT(TIMESTAMP(i.date,i.time),'%d.%m.%Y %H:%i') as `Date`, i.link page, ROUND(s1.p1_current,2) p1_current, ROUND(s1.p2_current,2) p2_current, ..., ROUND(s1.n_current,2) n_current,
ROUND(s2.p1_current,2) s2_p1_current, ROUND(s2.p2_current,2) s2_p2_current, ..., ROUND(s2.n_current,2) s2_n_current,
CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END AS "Custom name",
CASE
    WHEN (CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 10
        THEN 1500
    WHEN (CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 0.33
        THEN 1100
    WHEN (CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 0.44
        THEN 1200
    ELSE 0
END AS "Custom name 2", .....

from `item` i
inner join `system1` s1 on i.id = s1.item_id 
inner join `system2` s2 on i.id = s2.item_id
inner join `system3` s3 on i.id = s3.item_id 
inner join `student` st on st.id = i.student_id
inner join `table_name` table_name1 on i.material1_id = table_name1.id 
inner join `table_name` table_name2 on i.material2_id = table_name2.id
inner join `room` r on i.room_id = r.id

where s3.param550 in (CASE
    WHEN (CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 10
        THEN 1500
    WHEN (CASE
    WHEN s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 0.33
        THEN 1100
    WHEN (CASE
    WHEN s2.s2.p30_current = 0.22
        THEN 10
    WHEN s2.p30_current = 0.33
        THEN 11
    WHEN s2.p30_current = 0.44
        THEN 12
    ELSE 0
END ) = 0.44
        THEN 1200
    ELSE 0
END) ... 

union 

select t1.id `id`, concat(table_name1.name, ' - ', table_name2.name) `Material Name` ,st.Name "Student", r.name Room, concat(i.param1, ' - ', i.param2) `Param 1-2`, 
concat(COALESCE(s3.param1,'0'), ' - ', COALESCE(s3.param2, '0')) `Test Params`,DATE_FORMAT(TIMESTAMP(i.date,i.time),'%d.%m.%Y %H:%i') as `Date`, i.link page, ROUND(s1.p1_current,2) p1_current, ROUND(s1.p2_current,2) p2_current, ..., ROUND(s1.n_current,2) n_current,
ROUND(s2.p1_current,2) s2_p1_current, ROUND(s2.p2_current,2) s2_p2_current, ..., ROUND(s2.n_current,2) s2_n_current,
CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN ProductCount = 0.65
        THEN 11
    WHEN ProductCount = 0.77
        THEN 12
    ELSE 0
END AS "Custom name",
CASE
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 10
        THEN 1500
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 0.33
        THEN 1100
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 0.44
        THEN 1200
    ELSE 0
END AS "Custom name 2", .....

from `item` i
inner join `system1` s1 on i.id = s1.item_id 
inner join `system2` s2 on i.id = s2.item_id
inner join `system3` s3 on i.id = s3.item_id 
inner join `student` st on st.id = i.student_id
inner join `table_name` table_name1 on i.material1_id = table_name1.id 
inner join `table_name` table_name2 on i.material2_id = table_name2.id
inner join `room` r on i.room_id = r.id

where s3.param550 in (CASE
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 10
        THEN 1500
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 0.33
        THEN 1100
    WHEN (CASE
    WHEN s2.p30_current = 0.52
        THEN 10
    WHEN s2.p30_current = 0.65
        THEN 11
    WHEN s2.p30_current = 0.77
        THEN 12
    ELSE 0
END ) = 0.44
        THEN 1200
    ELSE 0
END) ...

Answer 1

read about using explain and view. judging by the request, you need to store intermediate data