MySQL join update running slowly

152
July 06, 2018, at 02:10 AM

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?

Answer 1

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

Rent Charter Buses Company
READ ALSO
Why take(5) operator in RxJava return all objects

Why take(5) operator in RxJava return all objects

I have problem with take(5) operator in rxjavaI don't why subscribe {ui

150
BorderLayout NORTH constraint prevents JScrollPane from scrolling

BorderLayout NORTH constraint prevents JScrollPane from scrolling

All of my code is written in a class that extends TopComponentI cannot show my add/remove methods, but am hoping that my issue is simple enough you will not need them

193
I want to display letter 'z' for each character in a String user enters

I want to display letter 'z' for each character in a String user enters

My problem is thisI need to make a String that is composed of letters 'z'

132