Optimize query multiple left join-CodePudding

I want to optimize this query:

SELECT m.*,
       m1.act_value   AS m1_login,
       m2.act_description AS m2_logout,
       m3.act_description           AS m3.warning
FROM   message m
       LEFT JOIN message_detail m1
              ON m.id = m1.message
                 AND m1.type= 'login'
                 AND m1.id_message = m.id
       LEFT JOIN message_detail m2 
              ON m.id = m2.message
                 AND m2.type= 'logout'
                 AND m2.id_message = m.id
       LEFT JOIN message_detail m3 
              ON m.id = m3.message
                 AND m3.type= 'warning'
                 AND m3.id_message = m.id
WHERE  m.state = 'SEND'
       AND m.time= 'Y'
       AND m.ip <> '192.168.1.2'
GROUP  BY m.id
ORDER  BY m.id

have you any ideas?

I want to perform a LEFT JOIN. How can I do this?

CodePudding user response：

You should use aggregation functions, since you already group. And then you can just join the message_detail table once, and distinguish using a CASE..WHEN expression:

SELECT m.id,
       MIN(CASE WHEN m1.type = 'login' THEN m1.act_value END)   AS m1_login,
       MIN(CASE WHEN m1.type = 'logout' THEN m1.act_description END) AS m2_logout,
       MIN(CASE WHEN m1.type = 'warning' THEN m1.act_description END) AS m3.warning
FROM   message m
       LEFT JOIN message_detail m1
              ON m.id = m1.message
                 AND m1.type in ('login', 'logout', 'warning')
                 AND m1.id_message = m.id
WHERE  m.state = 'SEND'
       AND m.time= 'Y'
       AND m.ip <> '192.168.1.2'
GROUP  BY m.id
ORDER  BY m.id

Note that in your SELECT clause you should either use values that are grouped by (or functionally dependent on them), or use some aggregation. So m.* is not good practice here, and would not be allowed on SQL standard compliant databases. See MySQL Handling of GROUP BY info on that topic and the ONLY_FULL_GROUP_BY setting.

CodePudding user response：

Bluntly speaking, it is a clumsy schema design to have so many tables. And it is the notorious EAV pattern.

Here's a better formulation without changing anything. It moves the LEFT JOINs into the SELECT part and avoids the GROUP BY.

SELECT m.*,
       ( SELECT act_value       FROM message_detail
            WHERE message = m.id AND type = 'login'  ) AS login,
       ( SELECT act_description FROM message_detail
            WHERE message = m.id AND type = 'logout' ) AS description,
       ( SELECT act_value       FROM message_detail
            WHERE message = m.id AND type = 'warning') AS act_description
FROM message AS m
    WHERE  m.state = 'SEND'
      AND  m.time= 'Y'
      AND  m.ip <> '192.168.1.2'
    ORDER BY  m.id

And you need these indexes:

message:  INDEX(state, time)
message_detail:  INDEX(message, type)

What is the PRIMARY KEY for message_detail? Please provide SHOW CREATE TABLE message_detail so I can advise on a bit more speedup by changing the PK.