scarhand Posted August 14, 2008 Share Posted August 14, 2008 will there be any major problems if i use a for loop, which could insert up to 1 million queries into the database? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 14, 2008 Share Posted August 14, 2008 what you are u inserting first? Quote Link to comment Share on other sites More sharing options...
scarhand Posted August 14, 2008 Author Share Posted August 14, 2008 what you are u inserting first? inserting 3 things into each row 1 is a 5-30 character sentence 1 is another 5-30 character sentence 1 is a bunch of content, up to about 5000 characters Quote Link to comment Share on other sites More sharing options...
corbin Posted August 14, 2008 Share Posted August 14, 2008 Just make sure you don't get max execution time errors. Besides that, you should be prepared for it to potentially take a while. If it's MySQL, I would insert mutliple values at a time (I would probably do blocks of 250, maybe more or less depending on the server). Ex: INSERT INTO tbl VALUES ('value1'), ('value2') That would make 2 rows. Quote Link to comment Share on other sites More sharing options...
toplay Posted August 14, 2008 Share Posted August 14, 2008 If you have the data already in a file, do not use INSERT but rather LOAD DATA INFILE. The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. FYI: The other day, I had to do 6,000 inserts in a MySQL InnoDB table on our production server (with live users active) and it took 4 minutes. Quote Link to comment Share on other sites More sharing options...
scarhand Posted August 14, 2008 Author Share Posted August 14, 2008 actually its now only going to be around 200,000 and i have to insert, and it has to be 1 by 1 because its done through a for loop Quote Link to comment Share on other sites More sharing options...
toplay Posted August 14, 2008 Share Posted August 14, 2008 That's still a lot of rows. You know your data best and what you're trying to do, however, you have not really shared why you think you need to do a loop? FYI: There are some things that you can dynamically do with the load data infile (like assign variables and do calculations before data is inserted). Good luck to you. Quote Link to comment Share on other sites More sharing options...
scarhand Posted August 14, 2008 Author Share Posted August 14, 2008 That's still a lot of rows. You know your data best and what you're trying to do, however, you have not really shared why you think you need to do a loop? FYI: There are some things that you can dynamically do with the load data infile (like assign variables and do calculations before data is inserted). Good luck to you. im taking content from static pages and inserting it into a database so it can be edited through a cms i scripted each static page will have its own row in the database the current amount of static pages is unbelievable, this is why i need to for loop and dump one by one Quote Link to comment Share on other sites More sharing options...
awpti Posted August 14, 2008 Share Posted August 14, 2008 Read 40-50 pages at a time into an array and create an extended insert statement. It'll run a bit faster than 1-by-1. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 14, 2008 Share Posted August 14, 2008 Yeah, LOAD DATA INFILE is the only way to go. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.