How to make inserting a million rows into MySQL faster/more efficient?

[diabolo]

function createChancePool($organizationID) {
$sql = "SELECT numChance FROM organizations WHERE id='$organizationID'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $numChances = $row['numChance'];
}
$table = "chancePool_$organizationID";
  $sql = "DROP TABLE IF EXISTS `$table`";
  mysql_query($sql);
  $sql = "CREATE TABLE `$table` (
          `chanceID` int(4) NOT NULL,
          `active` tinyint(1) NOT NULL,
          `member` int(7) NOT NULL,
          PRIMARY KEY  (`chanceID`)
       )";
  if(!mysql_query($sql)) {
        mysql_query("DROP TABLE IF EXISTS `$table`");
        return false;
  }

  $i = "0";
  while ($i < $numChances) {
    $sql = "INSERT INTO $table (chanceID, active, member) VALUES ('$i', '0', '0')";
      if(!mysql_query($sql)) {
        mysql_query("DROP TABLE IF EXISTS `$table`");
        return false;
      }
    $i++;
  }
$sql2 = "UPDATE organizations SET chancepool='1' WHERE id=$organizationID";  
mysql_query($sql2);

return true;
}

I have it set so the max is 7 digits, which is more than 1million, so i need to fix that. but 1 million is the max

and I have the script running right now, and its so far taking 5 minutes and im up to: 437,311 rows

and it stops there.
​

 

[descalzo]

Ideas:
1.
a. Make 'chanceID' auto increment
b. Set defaults for 'active' and 'member' to 0
c. $sql = "INSERT INTO $table VALUES (NULL)";

2. Alternatively, build the query ten (or more) values at a time......

   for ( $i=0; $i + 10 < $numChances; $i = $i + 10 ) {

       $sql = "INSERT INTO $table (chanceID, active, member) VALUES ('$i', '0', '0')";

       for( $j =$i + 1 ; $j < $i + 10; $j++ ){

           $sql .= ",('$j', '0', '0' )" ;
       }
       if(!mysql_query($sql)) {
           mysql_query("DROP TABLE IF EXISTS `$table`");
          return false;
      }
  }
  
#  might be off by one, doing this quickly
# do a dry run to 107 or so an print out $sql to make sure it is right

Or combine the two.
Then you just have to do the last $numChances % 10 .

 

[diabolo]

Ideas:
1.
a. Make 'chanceID' auto increment
b. Set defaults for 'active' and 'member' to 0
c. $sql = "INSERT INTO $table VALUES (NULL)";

2. Alternatively, build the query ten (or more) values at a time......

   for ( $i=0; $i + 10 < $numChances; $i = $i + 10 ) {

       $sql = "INSERT INTO $table (chanceID, active, member) VALUES ('$i', '0', '0')";

       for( $j =$i + 1 ; $j < $i + 10; $j++ ){

           $sql .= ",('$j', '0', '0' )" ;
       }
       if(!mysql_query($sql)) {
           mysql_query("DROP TABLE IF EXISTS `$table`");
          return false;
      }
  }
  
#  might be off by one, doing this quickly
# do a dry run to 107 or so an print out $sql to make sure it is right

Or combine the two.
Then you just have to do the last $numChances % 10 .

wouldn't your second method be more slower, since you have to run it though multiple loops. and what happens if an error happens midway, and it drops the table, wouldn't you lose all the time/hard work?

but i will try the first one

 

[descalzo]

and what happens if an error happens midway, and it drops the table, wouldn't you lose all the time/hard work?

The error code was copied from your code.

Combining the two methods and removing the inner loop,

   $leftover = $numChances % 10 ;
   $byTen = $numChances - $leftover ;
   for ( $i=0; $i + 10 < $byTen ; $i = $i + 10 ) {

       $sql = "INSERT INTO $table (chanceID) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)";

       mysql_query($sql);  
   }
 
  # code to do $leftover more entries

Another idea is to lock the table before the inserts and then unlock it afterwards.

 

[xav0989]

You said there could be up to a million rows, then to speed up the inserts, extend descalzo's method.

Start with % 100.
Take the leftovers and do % 10.
Finish up by doing the insert one by one.

This method will put more of the stress on the server, but the queries should be faster.