too long

Question

I want to insert 0 to 10000000 (10 million) of primary keys into my table.

My table is as follow:

p_key-->      (INT[10]) AUTO_INCREMENT PRIMARY KEY UNIQUE
hash_value--> (SMALLINT)
req_count--> (SMALLINT)

only adding 100 records is taking 11 seconds for 500 records it is giving 30 second timeout has been crossed.(wamp server localhost php mysql)

for ($i=0;$i<100;$i++){
    $query='INSERT INTO hash_table (hash_value, req_count) VALUES (0,0)';
    $result=$con->query($query);
    if(!$result){
        echo ("Error message:".$con->error." 
");
        exit();
    }
  }

Am I doing something terribly wrong.

Answer 1

You should insert more then one records at one SQL query.

For example:

$values='';

for ($i=0;$i<100;$i++){
    $values.='(0,0),';
}

$values=substr($values,0,-1);

$query='INSERT INTO hash_table (hash_value, req_count) VALUES '.$values;
$result=$con->query($query);
if(!$result){
    echo ("Error message:".$con->error." 
");
    exit();
}