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
isSIMPLE
.That's a full index scan
VisitDiffTimes
and range scan onCustomerVisits
.SUGGESTION #1
You need to reorder the columns in the PRIMARY KEY of
VisitDiffTimes
This will eliminate the full scan on
VisitDiffTimes
SUGGESTION #2 (Optional)
Create a compound index on those two columns for
CustomerVisits
The JOIN may end up cleaner
SUGGESTION #3 (Optional)
Set the join_buffer_size to 16M
Add this to
my.cnf
You don't have to restart. Just login as
root@localhost
and runGIVE IT A TRY !!!
SUGGESTION #4 (Optional)
Another Index with columns
(CustomerID,OrderID)