I am doing something similar. I pull from an Oracle database (without foreign keys) and am validating, adding keys, then loading into a MySQL database. This is for an online reporting app. The fastest solution I've found is something along the lines of:
- Dump each Oracle table into a tab-delim file. I do some validation here to limit the rows dumped. (This is actually the slowest part. Go figure.)
- Load each file into a MySQL table using LOAD FILE. I usually load about 25k rows/second.
- Create my foreign keys using UPDATE.
- Get rid of the bogus rows using DELETE.
- Issue OPTIMIZE TABLE to compact it (I'm using MyISAM).
- Add my indices.
Before anyone says anything, I know MyISAM doesn't have FK's per se. I don't ever modify this database outside the above data load.
I load about 10M rows in under 40 minutes this way, including all the FK relationships and indexing.
------
We are the carpenters and bricklayers of the Information Age.
Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose
I shouldn't have to say this, but any code, unless otherwise stated, is untested