Как изменить запрос для ускорение работы?

Question

[Dima Yakovlev]

Mysql(innodb), структура:
1) Предмет - общая информации о предмете, название, дата создания и т.п.(всего около 15 полей - varchar, int, datetime, ключ int автоинкрементен )
2) Показатели предмета в системе 1(80 полей - все double, кроме ключа Int) связь с первой по полю ключу связь 1к1.
3) Показатели предмета в системе 2(60полей - все double, кроме ключа Int) связь с первой по полю ключу связь 1к1.
4) Показатели предмета в системе 3(600полей - все double, кроме ключа Int) связь с первой по полю ключу связь 1к1.
5) Таблица student - 1000 записей 5 полей текстовых и Ключ связь 1 ко многим.
6) Таблица table_name - 1000 записей 5 полей текстовых и Ключ связь 1 ко многим.
7) Таблица room - 1000 записей 5 полей текстовых и Ключ связь 1 ко многим.
В каждой таблице по 70 000 записей, кроме таблиц 5-7 там до 1000 записей.
В зависимости от требований пользователя формируется sql запрос, в котором будут объединены несколько запросов через UNION, так как есть множество условий, для разных комбинаций показателей. Запрос может содержать как мат операции между полями таблиц, так и результатов CASE WHEN. Кол-во полей для select неограниченное, в условии - так же. Таких объединений может быть до 100.

Например:

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) ...

Comments

[Akina]

Смотрю я на это всё - ну бред голимый... особенно когда дошёл до автогенерируемого запроса - не удержался, заржал.

Чисто для демонстрации - фрагмент этого "автогенерёжу":

CASE ...

    WHEN (CASE  WHEN s2.p30_current = 0.52
                THEN 10
                WHEN ProductCount = 0.65
                THEN 11
                WHEN ProductCount = 0.77
                THEN 12
                ELSE 0
                END ) = 0.33
    THEN ...

[Sergey]

Здесь опечатка или на самом деле такой join?
inner join `student` st on i.id = i.student_id

[Dima Yakovlev]

Sergey, опечатка тут

[Dima Yakovlev]

Akina, ProductCount - это s2.p30_current, опечатка.

Есть лучший способ как то избежать CASE WHEN?

[Zhainar]

напишите задачу которую вы решаете, какие данные должны быть, не старайтесь придумывать решение за нас

Answer 1

[Антон Шаманов]

почитайте про использование explain и view. судя по запросу, вам нужно хранить промежуточные данные