Solved

MySQL (MariaDB) Update in Batches and loop until done?

Posted on 2016-07-14
8
84 Views
Last Modified: 2016-08-02
I have a couple of massive tables (T1 = 132,343,644 and T2 = 87,537,041) I need to update fields in T1 from field values in T2. Both have common 'recordID' fields that are indexed on each table.

Is there a way to run an update query in a batch type method so that its taking like 10,000 or 100,000 records at a time to update and then loop and grab the next batch until done. I have a feeling that this would be more efficient then my current update query.

UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL

Open in new window



Server is CENTOS 7.2.1511
CPU : 8 vCPU
Memory : 32,768
0
Comment
Question by:FirstDirect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 41711372
The way you have seems efficient assuming that there are indexes on the recordid's. If you want to do batches, you could either create partitions or simply use a where clause, like this:

UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL
AND T1.RecordID BETWEEN 0 and 1000000
1
 

Author Comment

by:FirstDirect
ID: 41711380
Is there a way to have it work in a LOOP? So that it processes the first million then skips to the next, the next until done?
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 41711388
There really aren't efficient loops in SQL. Why do you want a loop, when you can have it all done in one swoop? If you are worried about server activity, you can add LOW PRIORITY, if you want.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 24

Expert Comment

by:mankowitz
ID: 41711391
Or, if you really want to divide the job into batches, you could use WHERE or partitions.
0
 

Author Comment

by:FirstDirect
ID: 41711450
Hmm, not really what I was looking for.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 41711459
I suppose another option would be to make a stored procedure, but it adds unnecessary complexity.

CREATE PROCEDURE `update_all` ()
BEGIN

 DECLARE x  INT;
 
 SET x = 1;
 
 WHILE x  <= 5000000 DO
 UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL and T1.recordID < x; 
 SET  x = x + 1000000; 
 END WHILE;
 
END

Open in new window

1
 

Author Comment

by:FirstDirect
ID: 41716308
Mankowitz, I think you might be on to something. The other option since we have zip codes on the file. Would be to perhaps have it search and update records in a given zip code using a separate ZIP table as a reference table.

I have an index on ZIP5 (5 Digit Zip Code)
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 41717657
Again, this is an option, but still not the best way to do it.

CREATE PROCEDURE `update_all` ()
BEGIN

 DECLARE x  INT;
 
 SET x = 1;
 
 WHILE x  <= 99999 DO
 UPDATE MASTER AS T1
INNER JOIN `DATE-TYPE_07042016` AS T2 ON T1.recordID = T2.recordID
SET T1.DATE = T2.DATE, T1.NEW_USED = T2.NEW_USED_CODE
WHERE T1.NEW_USED IS NULL and T1.zip5 = x; 
 SET  x = x + 1; 
 END WHILE;
 
END

Open in new window

0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Joomla installation cannot see any mysql databases 4 61
Automate an Oracle update in Excel 7 70
Export individual report to pdf 2 41
Database movement 2 14
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question