I have one query that is taking too long(> 5 minutes) and creating deadlocks
virt_assist_bandwidth_incoming has 1115501 rows
virt_assist_bandwidth_incoming_media has 25752 rows
I do not think these are supper big tables.
The query:
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
When I do an explain plan
| 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) |
I tried adding the index that was suggested
The query still takes 20 minutes and the record count returned is 190682
Thanks for any help or insight
The biggest problem is that your tables use different character sets. This prevents the join to the second table from using an index.
My recommendation:
ALTER TABLE virt_assist_bandwidth_incoming CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE virt_assist_bandwidth_incoming_media CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
utf8mb4 is the default character set in current versions of MySQL. I think it's uncommon that you would ever need the other character sets.
Really it's the collation that needs to be the same in both columns you join. Of course they need to be the same character set as well, to have the same collation.
A further optimization can be to add an index that eliminates the filesort.
ALTER TABLE virt_assist_bandwidth_incoming ADD INDEX vidx_9 (from_id, sms_type, received);
I also recommend using InnoDB over MyISAM. InnoDB is the default storage engine in MySQL since 2010, and it has a lot of advantages over MyISAM, including performance. See my answer to MyISAM versus InnoDB
ALTER TABLE virt_assist_bandwidth_incoming ENGINE=InnoDB;
ALTER TABLE virt_assist_bandwidth_incoming_media ENGINE=InnoDB;