Queue up and run a batch of MySQL statements

I have a PHP script that is reading data from a set of FoxPro tables and inputting it into a parallel set of MySQL tables (in a single MySQL database). The process goes in this order:
  1. Clear MySQL tables
  2. Read through each record in each FoxPro table, inserting it into the corresponding MySQL table along the way

Obviously for a small amount of records, this works fine. However, this process is now working with hundreds if not thousands of statements and it's taking a much longer time to complete.

Is there a way for PHP / MySQL to queue these statements up and batch run them all at one time? It would also be helpful if it could lock the database prior to running this "batch transaction". Not sure what's possible here and what isn't.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I would leave the original MySQL tables alone until you are done.  Use temporary tables with a slightly different name to store the data from the FoxPro tables.  When you are done, 'drop' the original tables and 'alter' the name of the temporary tables to be the name that you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
I agree with Dave's comment regarding using a temporary talbe and then swap it with original.  You can use RENAME TABLE command.

Though for batch you can do:

- Read foxpro table and save them as CSV files
- Load CSV files to MySQL in batch using LOAD DATA INFILE.

- Accumulate records and generate batch inserts as

INSERT INTO...VALUES (...), (...), (...)


3) You may choose to explicite start-commit txn for the batch of insert:

-- 5000 INSERTS --
Vijaya KumarCommented:
Try the query like this

$sql = "insert into demo_store_rent (field1,field2,field3) values";

then looping through your select query $row like

foreach $rows in $row
in for each construct

$sql = $sql . "('". $rows[0] . "','" . $rows[1] . "','" . $rows[2] . ");

we have worked along this for 10000 records inserted in 15 to twenty seconds.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

street9009Author Commented:
The rename seems like the best option to me so far. The only other question I have is am I able to lock the database (for reads and writes) while I drop the original tables and rename the temp ones to their new names?

The other issue that can happen is that another process can attempt to read from these tables while they're empty or being "refilled". Of course a drop and rename should only take a fraction of a second, but it would be really helpful if any reads or writes in progress could be "paused" while that action is taking place.
theGhost_k8Database ConsultantCommented:
You can always do single command to swap tables:

RENAME TABLE original TO old_original, tmp_table TO original;

Open in new window

This is atomic
street9009Author Commented:
So reads from the tables while this is going on will be okay?
theGhost_k8Database ConsultantCommented:
When you execute RENAME TABLE, you cannot have any locked tables or active transactions. With that condition satisfied, the rename operation is done atomically; no other session can access any of the tables while the rename is in progress.
street9009Author Commented:
Oh! Very good. So it'll wait until it's free, do the rename, and then pick up. I like that.

Thanks all!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.