MySQL UPDATE query stuck on "Sending Data" State

Question

I am having difficulty getting an UPDATE query to successfully execute, due to the process getting stuck on "Sending Data" state.

I feel this differs from similar questions about the "Sending Data" state because it is happening during an UPDATE query, which MySQL documentation leads me to believe shouldn't happen. (?)

Here are the two relevant tables:

CREATE TABLE `CustomerVisits` (
 `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `OrderID` INT(10) UNSIGNED NOT NULL,
 `MerchantID` INT(10) UNSIGNED NOT NULL,
 `LocationID` INT(10) UNSIGNED NOT NULL,
 `CTime` INT(10) UNSIGNED NOT NULL,
 `CustomerID` INT(10) UNSIGNED NULL DEFAULT NULL,
 `SinceLastVisit` INT(10) UNSIGNED NULL DEFAULT NULL,
 PRIMARY KEY (`ID`),
 INDEX `CTime` (`CTime`) USING BTREE,
 INDEX `LocationID_OrderID_CTime` (`LocationID`, `OrderID`, `CTime`) USING BTREE,
 INDEX `OrderID` (`OrderID`),
 INDEX `CustomerID` (`CustomerID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
CREATE TABLE `VisitDiffTimes` (
 `DiffTime` DOUBLE NULL DEFAULT NULL,
 `OrderID` INT(10) UNSIGNED NOT NULL,
 `MerchantID` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 `CustomerID` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 `CTime` INT(10) UNSIGNED NULL DEFAULT NULL,
 PRIMARY KEY (`MerchantID`, `OrderID`, `CustomerID`) USING HASH,
 INDEX `DiffTime` (`DiffTime`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CustomerVisits has 12 million rows, and VisitDiffTimes has 4 million rows.

The CustomerVisits.SinceLastVisit column is currently all NULL - I want to populate it with the following query:

UPDATE CustomerVisits V
 JOIN VisitDiffTimes D
 ON V.OrderID = D.OrderID AND V.CustomerID = D.CustomerID
 SET V.SinceLastVisit = D.DiffTime

The output of EXPLAIN for this query is:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE D index NULL DiffTime 9 NULL 4065127 Using index
1 SIMPLE V ref OrderID,CustomerID OrderID 4 D.OrderID 1 Using where

Is there some problem with either my query or the table definitions that can make this operation faster?

---

Update

I have implemented RolandoMySQLDBA's suggestions below, but unfortunately the query is still running on and on without end.

I have included below the result of SHOW ENGINE InnoDB STATUS. I see that the engine is reporting 0.00 updates/s, so I am concerned that this query is not on the track towards completion in any reasonable time frame.

=====================================
2015年09月24日 15:55:40 7f9d02217700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 65705 srv_active, 0 srv_shutdown, 46262 srv_idle
srv_master_thread log flush and writes: 111963
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2282726
OS WAIT ARRAY INFO: signal count 3647755
Mutex spin waits 3344497, rounds 28268630, OS waits 400079
RW-shared spins 2898160, rounds 69659833, OS waits 1747791
RW-excl spins 365077, rounds 10516470, OS waits 86454
Spin rounds per wait: 8.45 mutex, 24.04 RW-shared, 28.81 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 26361458
Purge done for trx's n:o < 26361455 undo n:o < 0 state: running but idle
History list length 1926
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1595, OS thread handle 0x7f9d02217700, query id 70163629 (IP Address) (Username) init
SHOW ENGINE InnoDB STATUS
---TRANSACTION 26361457, ACTIVE 2764 sec fetching rows
mysql tables in use 2, locked 2
50350 lock struct(s), heap size 4617768, 181942 row lock(s)
MySQL thread id 1579, OS thread handle 0x7f9d02299700, query id 70163374 (IP Address) (Username) Sending data
UPDATE SaleOrderPayment P
 JOIN SaleOrderPayment_Diff_Merchant T
 ON P.OrderID = T.OrderID AND P.CustomerID = T.CustomerID
 SET P.SinceLastVisitMerchant = T.DiffTime
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
137002174 OS file reads, 20383973 OS file writes, 844094 OS fsyncs
18277.25 reads/s, 16771 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2723, seg size 2725, 5175728 merges
merged operations:
 insert 13665166, delete mark 8438509, delete 8253
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 415109, node heap has 71 buffer(s)
443.79 hash searches/s, 32426.94 non-hash searches/s
---
LOG
---
Log sequence number 125059941125
Log flushed up to 125059941125
Pages flushed up to 125059941125
Last checkpoint at 125059941125
0 pending log writes, 0 pending chkp writes
11664686 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 214630400; in additional pool allocated 0
Dictionary memory allocated 172905
Buffer pool size 12799
Free buffers 0
Database pages 12447
Old database pages 4602
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1352321, not young 1300347433
0.00 youngs/s, 147255.82 non-youngs/s
Pages read 144210003, created 530043, written 12368276
18709.33 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 876 / 1000, young-making rate 0 / 1000 not 983 / 1000
Pages read ahead 438.79/s, evicted without access 66.99/s, Random read ahead 0.00/s
LRU len: 12447, unzip_LRU len: 0
I/O sum[915059]:cur[17449], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 24315, id 140312316348160, state: sleeping
Number of rows inserted 36456765, updated 13112249, deleted 16214487, read 688045738
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 32871.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

---

Update 2

Here is the updated EXPLAIN output after following RolandoMySQLDBA's suggestions below.

id select_type table type possible_keys key key_le ref rows Extra
1 SIMPLE D index PRIMARY DiffTime 9 NULL 4044212 Using index
1 SIMPLE V ref CustomerID,OrderID_CustomerID CustomerID 5 D.CustomerID 1 Using where

---

Update 3

I have implemented RolandoMySQLDBA's Suggestion 4 below; the updated EXPLAIN output is:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE D index PRIMARY DiffTime 9 NULL 4044212 Using index
1 SIMPLE V ref OrderID_CustomerID,CustomerID_OrderID OrderID_CustomerID 9 D.OrderID,D.CustomerID 1 NULL

• RolandoMySQLDBA: Please add the explain plan of the query as it should look different with the indexes.
• djeffery: I have updated the question above, @RolandoMySQLDBA - please see "Update 2"
• djeffery: I have accepted your answer, @RolandoMySQLDBA, thanks for your help! The query has finally moved out of "Sending Data" state and is now "Updating Reference Tables" - so the query will probably complete within an hour or so. It seems that your latest index change made the OrderID_CustomerID index more appealing to the optimizer - perhaps I could have used FORCE INDEX instead, to similar effect?
• RolandoMySQLDBA: Doing FORCE INDEX might have done it as well. Try it our and see ...

Answer 1

PROBLEM

In the EXPLAIN plan, select_type is SIMPLE.

That's a full index scan VisitDiffTimes and range scan on CustomerVisits.

SUGGESTION #1

You need to reorder the columns in the PRIMARY KEY of VisitDiffTimes

ALTER TABLE VisitDiffTimes DROP PRIMARY KEY;
ALTER TABLE VisitDiffTimes ADD PRIMARY KEY (`OrderID`, `CustomerID`, `MerchantID`);

This will eliminate the full scan on VisitDiffTimes

SUGGESTION #2 (Optional)

Create a compound index on those two columns for CustomerVisits

ALTER TABLE CustomerVisits
 DROP INDEX OrderID,
 ADD INDEX OrderID_CustomerID_ndx (OrderID,CustomerID)
;

The JOIN may end up cleaner

SUGGESTION #3 (Optional)

Set the join_buffer_size to 16M

Add this to my.cnf

[mysqld]
join_buffer_size = 16M

You don't have to restart. Just login as root@localhost and run

mysql> SET GLOBAL join_buffer_size = 16 * 1024 * 1024;

GIVE IT A TRY !!!

SUGGESTION #4 (Optional)

Another Index with columns (CustomerID,OrderID)

ALTER TABLE CustomerVisits
 DROP INDEX CustomerID,
 ADD INDEX CustomerID_OrderID_ndx (CustomerID,OrderID)
;

• djeffery: Thank you @RolandoMySQLDBA - I have followed your three suggestions and the query is currently running. It's at about 11 minutes running in "Sending Data" state. As far as the select_type of SIMPLE, I feel that is to be expected, as I want every value in VisitDiffTimes to be matched up with the corresponding row(s) in CustomerVisits.
• RolandoMySQLDBA: Suggested another index. Please create the index under SUGGESTION #4, show the EXPLAIN plan, and try the query again.
• djeffery: Thanks @RolandoMySQLDBA - I have updated the question with EXPLAIN output following your suggested index change.