我有一个查询耗时太长(>；5分钟)并造成死锁
virt_assist_bandwidth_incoming有1115501行
虚拟辅助带宽传入媒体有25752行
我不认为这是晚餐大桌子.
询问:
SELECT *
FROM virt_assist_bandwidth_incoming i
LEFT JOIN virt_assist_bandwidth_incoming_media m ON m.messageID = i.messageID
WHERE i.from_id = '0'
AND i.sms_type = 0
ORDER BY received DESC
-
CREATE TABLE `virt_assist_bandwidth_incoming` (
`messageID` varchar(55) DEFAULT NULL,
`from` char(15) DEFAULT NULL,
`eventType` varchar(5) DEFAULT NULL,
`text` varchar(512) DEFAULT NULL,
`time` varchar(25) DEFAULT NULL,
`to` char(10) DEFAULT NULL,
`state` varchar(15) DEFAULT NULL,
`messageURL` varchar(105) DEFAULT NULL,
`applicationId` varchar(25) DEFAULT NULL,
`direction` varchar(10) DEFAULT NULL,
`vid` int(11) DEFAULT NULL,
`from_id` int(11) DEFAULT NULL,
`received` timestamp NULL DEFAULT NULL,
`process_status` varchar(255) DEFAULT NULL,
`sms_type` tinyint(4) DEFAULT NULL,
`acted_upon` tinyint(4) DEFAULT '0',
KEY `vidx_1` (`messageID`),
KEY `vidx_2` (`vid`),
KEY `vidx_3` (`sms_type`),
KEY `vidx_4` (`from_id`),
KEY `vidx_5` (`time`,`messageID`),
KEY `vidx_6` (`vid`,`process_status`,`acted_upon`),
KEY `vidx_7` (`vid`,`process_status`,`time`),
KEY `vidx_8` (`from_id`,`sms_type`,`messageID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
-
CREATE TABLE `virt_assist_bandwidth_incoming_media` (
`messageID` varchar(55) DEFAULT NULL,
`media` varchar(512) DEFAULT NULL,
KEY `vabimind_1` (`messageID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
当我做一个解释计划时
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | i | ref | "virt_assist_bandwidth_incoming_sms_type_index,virt_assist_bandwidth_incoming_from_id_index,vidx_8" | vidx_8 | 7 | "const,const" | 28670 | 100 | Using temporary; Using filesort | |
| 1 | SIMPLE | m | ALL | 25761 | 100 | Using where; Using join buffer (Block Nested Loop) |
我试着添加了建议的索引
查询仍然需要20分钟，返回的记录计数为190682
谢谢你的帮助或见解