I am attempting to perform a mass update on table containing 279,480 rows. The update must query another table 1,113,770. The update will select a set of records and and then calculate their sum and apply the result to a column in the table being updated.
The query works perfectly for single rows, but when applying to the full table, it fails with:
Error Code: 2013. Lost connection to MySQL server during query
This is being done inside a function. Here is the actual query. The invoiceheader table is the smaller table:
LEGACYINVOICENO is the PK for invoiceheader. SELLINGDEPARTMENT is also an indexed field as is the POSTDATE in the invoiceheader.
SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail
WHERE LEGACYINVOICENO IN(
SELECT LEGACYINVOICENO FROM invoiceheader
WHERE SELLINGDEPARTMENT = _department
AND POSTDATE < _postdate
AND LOTJOB_ID = _ljID) into _balance_used;
Updated Information. Here is a sample of the query actually working using a single instance:
SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail
WHERE LEGACYINVOICENO IN(
SELECT LEGACYINVOICENO FROM invoiceheader
WHERE SELLINGDEPARTMENT = 2
AND POSTDATE < '2013-06-06'
AND LOTJOB_ID = '45497100FOXLAND1640')
The execution plan indicates that the nested loop is resulting in a full table scan of the invoiceheader table. This seems a likely cause of the problem, but I'm not sure how I need to go about optimizing the inner query.
For testing purposes, I've simplified the query:
SELECT SUM(EXTENDEDPRICE)+SUM(TAX) FROM invoicedetail
WHERE LEGACYINVOICENO IN(
SELECT LEGACYINVOICENO FROM invoiceheader
WHERE LOTJOB_ID = '45497100FOXLAND1640')
LEGACYINVOCENO is the PK and LOTJOB_ID is indexed, but the WHERE clause is still resulting in a full table scan.
Don't use IN ( SELECT ... ), change to either a JOIN ... ON. This is likely to avoid the table scan.
Show us SHOW CREATE TABLE so we know what you are working with.
For large UPDATEs, chunk it.