Php – Large MySQL Batch Inserts From PHP: Run insert from script, or save as SQL file

file handlingMySQLPHPsql

We have a large dataset that's current residing in many, many spreadsheets.
I've been tasked with getting part of that data into our MySQL DB.
When all is said and done, I will probably be inserting somewhere around 3 million rows.

I intend to collect the inserts into batches of about a quarter-million or so (parsing through it with PHP).
Initially, my plan was to just run/send those batch INSERTs straight from my script.
More recently, though, I thought about actually writing out the INSERTs to .sql files for later importing.

I tried to google around on this a little … didn't find much.
Are there any real pro's/con's one way or the other?
If not, I'm inclined to stick with my original plan.
Otherwise, I'm completely open to suggestions.

Best Answer

The only major plus I see one way or the other is that by making the .sql files, if some problem occurs and you have to blow away the database and re-do the import process, you don't have to re-run your scripts against the spreadsheets. Or looking at it another way, you can test the process on a test server before doing the actual import.

But if this is just a one time thing, ultimately, either would suffice.

Related Solutions

MySQL – Best Way to Insert a Large Dataset into MySQL Database

Joe, your last comment explained a lot. I think the real problem is the data design. New columns may be needed when the document format changes, and in my experience document formats tend to change frequently. Instead of a 90-column table, with a single row per report, I would store the report data in a table with four columns: report_id, format_id, field_name, field_value. Each report would be represented by 90 rows, one for each field value in the report. This should simplify your code considerably.

Database – Implementing search over large data set, PHP or Mysql stored procedure

Most likely this should be done with SQL. This would be simpler and have better performance.

But would depend on a few details. Especially how exact or complicated you want it to be able to find the information the user typed in (maybe a list of available areas would be a better ui option here)

You could do a simple search like:

SELECT * FROM areas WHERE name LIKE 'input%';

This would of course fail for any typo the user makes and maybe return several records and you would have to offer the user further options to select one of those.

Assuming at some point you found a single record, it should be simple to find restaurants by area_id.

Best Answer

Related Solutions

MySQL – Best Way to Insert a Large Dataset into MySQL Database

Database – Implementing search over large data set, PHP or Mysql stored procedure

Related Topic