Slow updates with MySQL innoDB (clueless)

Question

I have a fairly simple query that runs super fast most of the time and takes up to 30 seconds in other rare occasions. I have no clue as to what is causing it. Here is an example of what I'm talking about.

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (3.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (1.88 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE matchSessionMembers msm JOIN matchSessions ms ON msm.matchNo = ms.matchNo AND ms.STATUS != 'N' SET msm.STATUS = 'P' WHERE msm.memberNo = 7 AND msm.STATUS = 'M';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

In this query I have set indexes for all msm.matchNo, ms.matchNo, ms.status, msm.status, and msm.memberNo.

I ran the same query 6 times and had 4 cases where the queries finished in under a second while 2 cases took more than a second to process. I did some more testing and got profile data for one of the more severe cases which took up to 30 seconds to execute this stupid query.

mysql> SHOW PROFILE FOR QUERY 3;  
+---------------------------+-----------+  
| Status                    | Duration  |  
+---------------------------+-----------+  
| starting                  |  0.000075 |  
| checking permissions      |  0.000013 |  
| checking permissions      |  0.000012 |  
| Opening tables            |  0.000073 |  
| checking permissions      |  0.000011 |  
| checking permissions      |  0.000013 |  
| System lock               |  0.000023 |  
| init                      |  0.000021 |  
| updating main table       |  0.000014 |  
| optimizing                |  0.000020 |  
| statistics                |  0.000117 |  
| preparing                 |  0.000028 |  
| executing                 |  0.000011 |  
| Sending data              | 31.940094 | <==
| updating reference tables |  0.000039 |  
| end                       |  0.000016 |  
| end                       |  0.000015 |  
| query end                 |  0.000048 |  
| closing tables            |  0.000039 |  
| freeing items             |  0.000036 |  
| logging slow query        |  0.000011 |  
| cleaning up               |  0.000014 |  
+---------------------------+-----------+  

I have a few other UPDATE queries with similar symptoms. Why is this query taking so long to execute even though there aren't any rows actually being updated? What could this "Sending data" bottleneck possibly be?

** edit: added CREATE TABLE code for both tables being used

CREATE TABLE matchSessionMembers (
matchNo int(11) NOT NULL,
memberNo int(11) NOT NULL,
status char(1) NOT NULL DEFAULT 'M' COMMENT 'M=match, P=pending, Y=saidYes, N=saidNo, D=deleted',
muted char(1) NOT NULL DEFAULT 'N',
deleted char(1) NOT NULL DEFAULT 'N',
regDatetime datetime DEFAULT NULL,
exitDatetime datetime DEFAULT NULL,
PRIMARY KEY (matchNo,memberNo),
KEY status (status),
KEY FK_matchSessionMembers_memberNo (memberNo),
KEY regDatetime (regDatetime),
KEY FK_matchSessionMembers_matchNo (matchNo),
CONSTRAINT FK_matchSessionMembers_matchNo FOREIGN KEY (matchNo) REFERENCES matchSessions (matchNo),
CONSTRAINT FK_matchSessionMembers_memberNo FOREIGN KEY (memberNo) REFERENCES members (memberNo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE matchSessions (
matchNo int(11) NOT NULL AUTO_INCREMENT,
memberCount int(11) NOT NULL,
status char(1) NOT NULL DEFAULT 'P' COMMENT 'P=pending, Y=successful, N=unsuccessful, X=expired',
isQuickMatch char(1) NOT NULL DEFAULT 'N',
open char(1) NOT NULL DEFAULT 'N',
regDatetime datetime DEFAULT NULL,
activeDate date DEFAULT NULL,
expireDate date DEFAULT NULL,
PRIMARY KEY (matchNo),
KEY status (status),
KEY activeDate (activeDate),
KEY expireDate (expireDate)
) ENGINE=InnoDB AUTO_INCREMENT=113912 DEFAULT CHARSET=utf8

Answer 1

I figured it out.

A stupid mistake on my part. It wasn't easy to identify though. I had a different super slow query that ran every hour or so, one update query that was taking over 30 seconds to execute. I'm not perfectly sure but I think that that query must have locked everything down and put all other queries in queue.

I didn't notice it in the slow query logs because it was buried under all the other slow queries that resulted of this one dumb query.

I hope this helps anyone else having similar problems.