HELP: speed of inserting LOTS of records(10,000,000) into mysql using php

[zjcboy]

This is the table structure in mysql:
---------------------------------------------------
create table test(
a1 char,
a2 char,
a3 char,
a4 char,
a5 char,
a6 char,
a7 char,
id unsigned int primary auto_increment);
---------------------------------------------------

This is the php code to insert LOTS of records into the table:
---------------------------------------------------

for($a1=0;$a1<=9;$a++){
  for($a2=0;$a2<=9;$a++){
    for($a3=0;$a3<=9;$a++){
      for($a4=0;$a4<=9;$a++){
        for($a5=0;$a5<=9;$a++){
          for($a6=0;$a6<=9;$a++){
            for($a7=0;$a7<=9;$a++){
              mysql_query("insert into test set a1=$a1,a2=$a2,a3=$a3,a4=$a4,a5=$a5,a6=$a6,a7=$a7")
             }
           }
         }
      }
    }
  }
} 

---------------------------------------------------
this code would take a very long time to finish inserting records and IIS gives a CGI Timeout Error. I'd like to know how I could solve this problem. I'll be thankful for your reply.

[Cam]

I don't know if this would work the same because you are using the CGI executable but try adding this line at the start of your script.

set_time_limit(0); 

[zjcboy]

I have already set PHP time out limit to 0. the problem is that IIS gave out an timeout error message.help......

[Chris82]

I doubt that you will have much luck using php for this. If you can run java here is a simple class which creates a file of comma separated values which can later be used with the mysql command to load data from a file.

public class FileThing {
	public static void main(String[] args) {
		int a1, a2, a3, a4, a5, a6, a7;
		
		for(a1=0;a1<=9;a1++){
		  for(a2=0;a2<=9;a2++){
			for(a3=0;a3<=9;a3++){
			  for(a4=0;a4<=9;a4++){
				for(a5=0;a5<=9;a5++){
				  for(a6=0;a6<=9;a6++){
					for(a7=0;a7<=9;a7++){
					  System.out.println(
					  	a1 + ", " +
					  	a2 + ", " +
						a3 + ", " + 
						a4 + ", " + 
						a5 + ", " + 
						a6 + ", " +
						a7);
					 }
				   }
				 }
			  }
			}
		  }
		}   
		
	}
}

Usage:

- javac *.java
- java FileThing >temp

This took about 5-10 minutes on my PC and created a 200 MB large file.

[zjcboy]

There is no Java on my PC. Could I use php instead?

[kylemaxwell.com]

Why not insert a thousand (or a hundred thousand), refresh the page with the header function, and insert another thousand. You could keep track of where you are by passing a variable through the querystring.

Also, I hope that is sample code, cause I see no good use for inserting numbers over and over in a predictable pattern.

[samsm]

There is no Java on my PC. Could I use php instead?

You have Perl? Perl could pull it off in a jiffy.

Also, I hope that is sample code, cause I see no good use for inserting numbers over and over in a predictable pattern.

I'd be inclined to agree.

[zjcboy]

You have Perl? Perl could pull it off in a jiffy.

No Perl, either

[Chuckie]

There is no Java on my PC. Could I use php instead?

Why not download it?
Java

[zjcboy]

Why not download it?
Java

Because I don't know how to program with Java

[samsm]

Because I don't know how to program with Java

Chris82 did all the programming, you could just download Java and follow his instructions.

Anyway, just occured to me, you don't have to use the webserver, you can execute the database altering script using the php executable. That shouldn't be affected by web server timeout.

[poLka]

maybe it's an IIS thing?

http://www.sitepointforums.com/showt...ht=iis+timeout

[zjcboy]

I doubt that you will have much luck using php for this. If you can run java here is a simple class which creates a file of comma separated values which can later be used with the mysql command to load data from a file.

public class FileThing {
	public static void main(String[] args) {
		int a1, a2, a3, a4, a5, a6, a7;
		
		for(a1=0;a1<=9;a1++){
		  for(a2=0;a2<=9;a2++){
			for(a3=0;a3<=9;a3++){
			  for(a4=0;a4<=9;a4++){
				for(a5=0;a5<=9;a5++){
				  for(a6=0;a6<=9;a6++){
					for(a7=0;a7<=9;a7++){
					  System.out.println(
					  	a1 + ", " +
					  	a2 + ", " +
						a3 + ", " + 
						a4 + ", " + 
						a5 + ", " + 
						a6 + ", " +
						a7);
					 }
				   }
				 }
			  }
			}
		  }
		}   
		
	}
}

Usage:

- javac *.java
- java FileThing >temp

This took about 5-10 minutes on my PC and created a 200 MB large file.

I've just downloaded Java and tried. Wow! It's really cool~~~ it took 10 minutes in total instead of 2 hours using PHP~~~
Thank you very much!!!!

[zjcboy]

Chris82 did all the programming, you could just download Java and follow his instructions.

Anyway, just occured to me, you don't have to use the webserver, you can execute the database altering script using the php executable. That shouldn't be affected by web server timeout.

I tried the method Chris82 had offered and found that is a very fast way to insert these records. I also tried php executable. It took 2 hours to finish the job while Java took only a few minutes. Thanks for your guide!