List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:May 21 2004 5:18am
Subject:Re: Repeat loops in mysql, large data issue, suggestions wanted
View as plain text  
>>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?
>
>  
>
What that means is you do 100,000 inserts, and then do one commit at the 
end. If the connection to the database dies, or the database itself 
crashes, then all the rows inserted will be rolled back, and no data 
will be in your database.

>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?
>  
>

No, you still have to do all the inserts, but either they all get in, or 
none of them get in, depending on what happens as you are inserting.

>>"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".
>
>  
>
I haven't seen a function like that; it might exist. It might not.

>>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.
>
>  
>
Not sure I follow. If you wrote it in C, and compiled it on the machine 
where teh MySQL database was.... or if you wrote it in Java, it could 
run anywhere. You can also write it in PERL and run it on the database 
machine, assuming you install PERL.

>>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?
>  
>
I don't think so. But IMPORT DATA doesn't require any coding. For 
example, you  just put this into a file:

LOAD DATA LOCAL INFILE 'file_with_all_the_data.txt'
 INTO table the_table_where_rows_go
 FIELDS TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''
 LINES TERMINATED BY '\r\n';

This reads a file, and breaks each line up by a \t (and the line ends 
with \r\n in this example). It just dumps all the data into the table. 
The columns in the table have to be in the same order as the fields in 
each line.

No coding required.

David

Thread
Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
  • Re: Repeat loops in mysql, large data issue, suggestions wantedDavid Griffiths21 May
    • Re: Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
      • Re: Repeat loops in mysql, large data issue, suggestions wantedDavid Griffiths21 May
        • Re: Repeat loops in mysql, large data issue, suggestions wantedScott Haneda21 May
Re: Repeat loops in mysql, large data issue, suggestions wantedbeacker21 May
  • Large sample data sets for testingGreg Willits21 May
    • Re: Large sample data sets for testingDan Nelson21 May
    • R: Large sample data sets for testingLeonardo Francalanci22 May
      • Re: R: Large sample data sets for testingmos22 May