MYSQL查询优化连接4个表

Question

需要帮助来优化此查询

这个任务是从friends表中选择我的朋友，检查他们中是否有任何人在忽略表中，从users表中获取他们的信息，也从messages表中获取最后一条消息的日期。我的朋友也可以是群里的。

我的问题是：

SELECT
        `u`.`id`, `u`.`login`, `u`.`name`, `u`.`surname`,
        CONCAT(`u`.`name`, ' ', `u`.`surname`) AS `namesurname`, `u`.`avatar`,
        `u`.`status`, `u`.`status_text`, `u`.`last_active`, `u`.`type`,
        `f`.`status` AS `friend_state`, `i`.`date` AS `ignore_date`,
        MAX(`m`.`date`) AS `last_message_date` 
    FROM `friends` AS f 
    INNER JOIN `users` AS u ON `u`.`id`=`f`.`friend_id`
    LEFT JOIN `ignore` AS `i` ON `i`.`uid`='10' AND `i`.`ignore_id`=`f`.`friend_id`
    LEFT JOIN `messages` AS `m` ON `m`.`date`>DATE_ADD(NOW(), INTERVAL -1 DAY) AND 
    (
        (`m`.`fromid`=`u`.`id` AND `m`.`toid`='10') OR 
        (`m`.`toid`=`u`.`id` AND `m`.`fromid`='10') OR
        (`m`.`toid`=`u`.`id` AND `m`.`toid` IN (16,22,27))
    ) 
    WHERE `f`.`uid`='10'
    GROUP BY `u`.`id`
    ORDER BY `last_message_date` DESC, `namesurname`

对于170k消息，此查询耗时2.2-2.5秒。

如果我删除

(`m`.`toid`=`u`.`id` AND `m`.`toid` IN (16,22,27))

时间为0.55秒

10 -是我的用户ID

16,22,27 -群的id

我需要这行(m.toid=u.id和m。toid IN (16,22,27))，因为在群聊中，它不应该依赖于谁发送了最后一条消息。

提前感谢

Answer 1

您可能不需要LEFT。假设这样，我会将ORs转换为UNIONs，并从以下内容开始：

由于您没有提供SHOW CREATE TABLE，因此我猜测messages具有PRIMARY KEY(mid)。

SELECT ...
    FROM ( ( SELECT mid FROM messages WHERE fromid = u.id AND toid = 10 )
           UNION ALL 
           ( SELECT mid FROM messages WHERE toid = u.id AND fromid = 10 )
           UNION ALL 
           ( SELECT mid FROM messages WHERE toid = u.id AND toid IN (16,22,27) ) AS um
    JOIN users AS u  ON ...
    JOIN 

哦，相反，我将首先尝试在messages中做尽可能多的事情，假设1天的限制是一个重要的过滤器。(请注意，估计需要扫描170K行。)

首先检查这个，看看它是否“快速”，并提供不“太多”的行：

SELECT COUNT(*) FROM (
        ( SELECT toid AS either_id, MAX(date) AS last_date FROM messages
             WHERE toid IN (10, 16,22,27) AND date > NOW() + INTERVAL 1 DAY )
        UNION ALL   -- or, if you get dups, UNION ALL
        ( SELECT fromid, MAX(date) FROM messages
             WHERE fromid = 10 AND date > NOW() + INTERVAL 1 DAY )
                     ) um;

假设这是可以的，然后继续

SELECT ...
    FROM ( ... ) AS um   -- the derived table above
    JOIN users AS u  ON um.either_id = u.id
    JOIN ...   -- the rest of the stuff

索引...

messages:  (toid, date)
messages:  (fromid, date)

(这是一个非常混乱的查询，所以我没有信心自己能帮上忙。)