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?
Dave BaldwinConnect With a Mentor Fixer 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.
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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_k8Connect With a Mentor Database 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_k8Connect With a Mentor Database 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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.