Performance Comparison of Inserting Integers vs. Strings

MySQL Tutorials - Herong's Tutorial Examples

∟Performance Comparison of Inserting Integers vs. Strings

This section provides a tutorial example on how to check performance comparison between inserting integers and inserting strings to MySQL database.

As a comparison, I created another PHP script to measure the speed of inserting strings into database.

<?php
# speed-of-insert-string.php
# Copyright (c) 2005 HerongYang.com. All Rights Reserved.
#
  $count = 100;
  if (count($argv)>1) $count = $argv[1];

  $binLog = "on";
  if (count($argv)>2) $binLog = $argv[2];

  $charset = "utf8mb4";
  $collate = "utf8mb4_0900_ai_ci";
  if (count($argv)>3) $charset = $argv[3];
  if ($charset=="utf8") {
    $collate = "utf8_unicode_ci";
  } else if ($charset=="utf8mb4") {
    $collate = "utf8mb4_0900_ai_ci";
  } else {
    print("Unknown character set: $charset\n");
    die;
  }

  print("Testing parameters:\n");
  print("   Number of operations: ".$count."\n");
  print("   Binary log = $binLog\n");
  print("   Character set = $charset\n");

  $con = mysqli_connect("127.0.0.1", 'herong', 'TopSecret', "test");

  $sql = "SET sql_log_bin = $binLog";
  $res = mysqli_query($con, $sql);

  $sql = "CREATE TABLE Tests (Message CHAR(4))"
    . " ENGINE=InnoDB DEFAULT CHARSET=$charset COLLATE=$collate";
  $res = mysqli_query($con, $sql);

  $startTime = round(microtime(true)*1000);
  $i = 0;
  while ($i < $count) {
    $str = substr(strval(10000+$i), -4);
    $sql = "INSERT INTO Tests VALUES ('$str')";
    $res = mysqli_query($con, $sql);
    $i++;
  }
  $endTime = round(microtime(true)*1000);
  $exeTime = $endTime - $startTime;
  print("Testing result:\n");
  print("   Execution time in milliseconds = ".$exeTime."\n");
  print("   Milliseconds per operation = ".round($exeTime/$count)."\n");

  $sql = "SELECT COUNT(*) FROM Tests";
  $res = mysqli_query($con, $sql);
  $row = mysqli_fetch_array($res);
  print("   Records inserted = ".$row[0]."\n");

  $sql = "DROP TABLE Tests";
  $res = mysqli_query($con, $sql);
  mysqli_close($con);
?>

1. Compare performance of inserting strings against inserting integers to the MySQL server on my CentOS 8 computer. I see that inserting strings takes about the same time as inserting integers.

herong$ php speed-of-insert-string.php 100
Testing parameters:
   Number of operations: 100
   Binary log = on
   Character set = utf8mb4
Testing result:
   Execution time in milliseconds = 5303
   Milliseconds per operation = 53
   Records inserted = 100

herong$ php speed-of-insert-string.php 1000
Testing parameters:
   Number of operations: 1000
   Binary log = on
   Character set = utf8mb4
Testing result:
   Execution time in milliseconds = 60823
   Milliseconds per operation = 61

herong$ php speed-of-insert-integer.php 100
Testing parameters:
   Number of operations: 100
   Binary log = on
Testing result:
   Execution time in milliseconds = 5295
   Milliseconds per operation = 53
   Records inserted = 100

herong$ php speed-of-insert-integer.php 1000
Testing parameters:
   Number of operations: 1000
   Binary log = on
Testing result:
   Execution time in milliseconds = 59162
   Milliseconds per operation = 59
   Records inserted = 1000

2. Check impact of binary logging when storing strings in MySQL database on my CentOS 8 computer. I see that there is almost no impact by turning off binary logging.

herong$ php speed-of-insert-string.php 100 off
Testing parameters:
   Number of operations: 100
   Binary log = off
   Character set = utf8mb4
Testing result:
   Execution time in milliseconds = 5319
   Milliseconds per operation = 53
   Records inserted = 100

3. Check impact of character encoding when storing strings in MySQL database on my CentOS 8 computer. I see that there is almost no impact by switching from utf8mb4 to utf8.

herong$ php speed-of-insert-string.php 100 on utf8
Testing parameters:
   Number of operations: 100
   Binary log = on
   Character set = utf8
Testing result:
   Execution time in milliseconds = 5245
   Milliseconds per operation = 52
   Records inserted = 100