CODEWITHSUNDEEP
phpmysqltransactions
Mattia Dinosaur
Mattia Dinosaur

Reputation: 920

Why submit transaction every row is so slow?

The table is empty at first .
when i commit the transaction after 20000 row ,it cost less than 1 second . enter image description here

  $stmt = $conn->prepare("INSERT INTO ipTable (ip) VALUES (?)");
  $stmt->bind_param("i", $ip);
  $conn->query('BEGIN');
  for( $count = 0  ; $count < 20000 ; $count ++){
  $ip = rand(1,10000000);
  $stmt->execute();
  }
  $conn->query('COMMIT');
  $stmt->close();
  $conn->close();

with time command

time php test.php



real    0m0.785s
user    0m0.220s
sys     0m0.096s

But when I commented out the $conn->query('BEGIN'); $conn->query('COMMIT'); , it run more than 20 minute and don't know how much time i have to wait to finish .I have to stop it with ctrl-c . 

      $stmt = $conn->prepare("INSERT INTO ipTable (ip) VALUES (?)");
      $stmt->bind_param("i", $ip);
       //$conn->query('BEGIN');
      for( $count = 0  ; $count < 20000 ; $count ++){
      $ip = rand(1,10000000);
      $stmt->execute();
      }
      //$conn->query('COMMIT');
      $stmt->close();
      $conn->close();

enter image description here

Upvotes: 2

Views: 57

Answers (1)

Mohammad C
Mohammad C

Reputation: 1341

Try turning on autocommit

$conn->autocommit(TRUE);

$conn->autocommit(TRUE);
$stmt = $conn->prepare("INSERT INTO ipTable (ip) VALUES (?)");
$stmt->bind_param("i", $ip);
//$conn->query('BEGIN');
for( $count = 0  ; $count < 20000 ; $count ++){
    $ip = rand(1,10000000);
    $stmt->execute();
}
//$conn->query('COMMIT');
$stmt->close();
$conn->close();

Upvotes: 1

Related Questions