MySQL Optimization – UPDATE Query Stuck on Sending Data State

innodbMySQLmysql-5.6optimizationupdate

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

Best Answer

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)
;