CODEWITHSUNDEEP
mysqljoinsql-update
juacala
juacala

Reputation: 2235

MySQL join update running slowly

I'm running MySQL 5.6.33, and have an update query that is running quite slowly, and I don't understand why.

I have two tables: there are 35,908 rows in alltranscur, and there are 86,103 rows in acctnumcust.

The query:

update alltranscur a inner join acctnumcust d
on a.acctNumber=d.acctNum
set
a.custID=d.custID

Takes a very long time (5 min 58.29 sec, matching ~22,000 rows). I've also alternatively written it as:

update alltranscur a, acctnumcust d
set
a.custID=d.custID
where a.acctNumber=d.acctNum

with the same result.

The table create statements are:

CREATE TABLE `alltranscur` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`custID` varchar(200) DEFAULT NULL,
`acctNumber` int(11) DEFAULT NULL,
... other columns
PRIMARY KEY (`id`),
KEY `acctNumber` (`acctNumber`),
... other keys
CONSTRAINT `alltranscur_ibfk_8` FOREIGN KEY (`custID`) REFERENCES `custdata` (`custID`) ON UPDATE CASCADE
... other foreign keys
) ENGINE=InnoDB AUTO_INCREMENT=5226303 DEFAULT CHARSET=latin1

and

CREATE TABLE `acctnumcust` (
`acctnum` varchar(50) NOT NULL,
`custid` varchar(200) NOT NULL,
PRIMARY KEY (`acctnum`),
KEY `custid` (`custid`),
KEY `acctnum` (`acctnum`,`custid`),
CONSTRAINT `acctnumcust_ibfk_1` FOREIGN KEY (`custid`) REFERENCES `custdata` (`custID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

If I

explain
update alltranscur a inner join acctnumcust d
on a.acctNumber=d.acctNum
set
a.custID=d.custID

I get:

+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+
| id | select_type | table | type  | possible_keys   | key        | key_len | ref           | rows  | Extra       |
+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+
|  1 | SIMPLE      | d     | index | PRIMARY,acctnum | custid     | 202     | NULL          | 85152 | Using index |
|  1 | SIMPLE      | a     | ref   | acctNumber      | acctNumber | 5       | pcb.d.acctnum |     1 | Using where |
+----+-------------+-------+-------+-----------------+------------+---------+---------------+-------+-------------+

I would have thought that MySQL would have used the PRIMARY key on acctnum, or maybe even the combination key on acctnum/custID. But it doesn't.

Is this just as fast as a query like this goes? I've done joined updates on similar sized tables in the past, and they didn't take nearly as long. Am I missing something about these tables? Is there a way to make it run faster?

Upvotes: 1

Views: 53

Answers (1)

Thomas G
Thomas G

Reputation: 10226

The root cause of you problem is more than probably the fact that you are joining your 2 tables on columns having different datatypes

`acctNumber` int(11) DEFAULT NULL,
`acctnum` varchar(50) NOT NULL,

where a.acctNumber=d.acctNum

This means that MySQL can't use the indexes directly and end up in a full table scan.

Change the datatype of column acctnum in table acctnumcust to INT(11) and your perf issue should be solved

Upvotes: 2

Related Questions