Inserting php arrays into mysql

is there a way to do this

[ionchannels]

Hi, I am working on a project where I am dealing with over 10,000,000 records and I was wondering if it is possible to slice these records into multidimensional arrays of say 1,000,000 records each and then insert them into mysql with 10 inserts statements rather than the 10,000,000 insert statements I am currently using... I know there is a way to extract data to an array, but is there a way to then take an array and plug it into mysql without doing millions of iterations?

Thanks in advance...

[ergophobe]

Not really. MySQL has no array data type. You could serialize [us3.php.net] the array and then put that into the DB, but it would just put many values in one record and not do at all what you want.

Also, keep in mind that when you extract data to an array, that puts each record in an array, there is no built-in function to put many records in an array which would be the analogue of what you're talking about.

What format is the data in currently and how big is it in bytes? The main DB I spend most of my time manipulating is only about 12MB currently, but that's still too big for me to want to do mass inserts, updates, backups over my slow network with PHP. If I need to transfer it, or something, I generally have PHP write out that SQL queries to a text file, and then feed that in via the command line client.

One thing to keep in mind as well, an array of 1,000,000 records, each with 1K of data is 1GB that you would be trying to put in memory as an array. That would tax if not crash your computer.

[sned]

There was some discussion in this topic - [webmasterworld.com ] - about large volumes of inserts. I don't know if you could apply it to your situation, but might be worth checking out.

-sned

[ergophobe]

Look there if you *have* to do these inserts with PHP. Otherwise, try to find a way to do the mass inserts from the MySQL command line.

[ionchannels]

Thanks very much for all the great suggestions. Your point is well taken about all the memory taken up by a multidimensional array. As suggested, the best "solution" for me was to chain the insert statements into one and do 10 at a time (I suppose one could do a infinite number at once, but there must be a limit to the length of an $sql="INSERT blah"; query. I find that 10 is good as it reduces the number of connections to make to only 1M and is easy on my box's memory. Thanks again...

[coopster]

The thread mentioned by sned contains a link describing INSERT speeds. It is a very good read and will prove to be quite helpful to you. It discusses many options and even alternatives (such as what ergophobe suggested). In the end, do what is going to work for you, but that read should help you fine tune things.

[ergophobe]

Sorry, I didn't mean to suggest that he not read that thread. I just meant to say that either way, if you can take PHP out of it and interact directly with the MySQL server it will be all the better.

[jatar_k]

if all the inserts are in a file, then just use the command line

at the prompt

mysql -u username -p dbname < /path/to/file/forinserts.txt

though that only works if you have insert statements already

though if not you could have php read the file, create the insert statement and write it to a new file that you could use as above.

[coopster]

Sorry, I didn't mean to suggest that he not read that thread.

hehe, I knew that, ergo. I wasn't thinking that at all! The emphasis I was making is that the referenced links in the MySQL manual go into both options and that ultimately ionchannels could make a much better decision after contemplating the options. Personally, the command line would be my first choice if it didn't have to be scripted. Much, much faster.