我有一张聊天消息表，该表已增长到> 4m行。
我需要在特定日期范围内向用户发送消息和从用户发送消息。
最初，此查询还可以，但是由于当前表的大小占用的时间过长（> 10s）。
我尝试创建许多索引组合； MySQL在messageType + sentDate上使用复合索引
SELECT `ofMessageArchive`.*
FROM `ofMessageArchive`
WHERE `ofMessageArchive`.`messageType` IN ('Message')
AND (ofMessageArchive.fromJID = '[email protected]' OR ofMessageArchive.toJID = '[email protected]')
AND (ofMessageArchive.sentDate > '1462235333109')
ORDER BY ofMessageArchive.sentDate ASC LIMIT 50
有什么想法可以优化此查询吗？
[编辑]
EXPLAIN结果：+----+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------+--------+-------------+
| 1 | SIMPLE | ofMessageArchive | ref | index_ofMessageArchive_on_fromJID_and_toJID,index_ofMessageArchive_on_sentDate,index_ofMessageArchive_on_messageType,idxMessageTypeSentDate,ofMessageArchive_fromjid_idx,ofMessageArchive_tojid_idx | idxMessageTypeSentDate | 767 | const | 731570 | Using where |
+----+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+-------+--------+-------------+
最佳答案
您遇到了将传出消息与传入消息区别对待的问题（基本上是规范化问题），因此您不能直接在用户名上使用索引，这可能是您最重要的值。
如果还没有，请创建索引ofMessageArchive(fromJID, messageType, sentDate)并ofMessageArchive(toJID, messageType, sentDate)（可能以不同的顺序，具体取决于您对这些列中的每一个有多少个不同的值以及您使用的其他查询，但从此顺序开始）。
要使用这些索引，请使用union：
select * from
( (SELECT `ofMessageArchive`.*
FROM `ofMessageArchive`
WHERE (ofMessageArchive.fromJID = '[email protected]')
AND `ofMessageArchive`.`messageType` IN ('Message')
AND (ofMessageArchive.sentDate > '1462235333109')
ORDER BY ofMessageArchive.sentDate ASC LIMIT 50
) union
(SELECT `ofMessageArchive`.*
FROM `ofMessageArchive`
WHERE (ofMessageArchive.toJID = '[email protected]')
AND `ofMessageArchive`.`messageType` IN ('Message')
AND (ofMessageArchive.sentDate > '1462235333109')
ORDER BY ofMessageArchive.sentDate ASC LIMIT 50
)
) s
order by s.sentDate ASC LIMIT 50