Re: Repeat loops in mysql, large data issue, suggestions wanted

[Scott Haneda]

on 05/20/2004 09:36 PM, David Griffiths at dgriffiths@stripped wrote:

> You didn't mention the table-type (storage-engine) you were planning on
> using, but if you use InnoDB, and do it all in one transaction (ie turn
> off auto-commit and commit just once at the endi), then any failure
> during the mass-insert will cause a rollback, so you don't have to worry
> about the integrity of your data.

I can use any I like, I wont have not even built the DB yet, so I am open to
any suggestions.

I don't see how I can do it all in one transaction, what does that mean?

I think you may mean something like rather than doing 100,000 separate
inserts, somehow build that into just one insert string, so it is then one
connection?  Can you elaborate?

> "Is it then possible to have a SQL statement that would somehow take the
> lump field data, select it, chop it up, and insert 100,000 rows, in this
> case, three fields per row?"
> 
> I haven't heard of a SQL function like that. In addition, you'll suffer
> the pain twice by inserting it once as one big field, selecting it out
> again, breaking it up, and then re-inserting it.

I was thinking that perhaps MySql would be more efficient at it than some
server side "middleware".

> Is the code going to be running on the machine with the database? That
> could improve it.

The code that would do the insert will run on hardware that is on the same
network as MySql, but certainly not the same machine, they are 2 different
OS's so this is not possible to run them on the same machine.

> One other thing to consider is to use "IMPORT DATA" to do a bulk load
> rather than a tonne of insert statements. You can do this from a
> command-line on the machine where the MySQL server is installed. See the
> docs at http://www.mysql.com to get the syntax of the IMPORT DATA - it
> should work on the format of the file as you specified below.

Can import data be used on MySql if the data is not on the same machine as
MySql?

-- 
-------------------------------------------------------------
Scott Haneda                                Tel: [phone]
http://www.newgeo.com                       Fax: [phone]
scott@stripped                            Novato, CA U.S.A.