Mysql Question posted in
The official MySQL documentation can be found here.

getting mysql deadlock because this query is taking to long

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

Tags:

2

Answers


  1. 0

    How many rows do you have where the from_id=0 and sms_type=0 out of the 1115501 rows? This ratio will determine if the optimizer chooses an index.

    You can create an index that matched better with the query:

    create index vidx_8
on virt_assist_bandwidth_incoming (from_id, sms_type, messageID);

    Also, try adhering the datatypes, if i.from_id is an integer, use i.from_id=0 instead of i.from_id='0'.

    There was no table description, so there is no info on from which table the received comes from.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search