Performance Comparison of Inserting Integers vs. Strings

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) 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.

centos$ 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

centos$ 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

centos$ 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

centos$ 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.

centos$ 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.

centos$ 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

Table of Contents

 About This Book

 Introduction to Linux Systems

 Cockpit - Web Portal for Administrator

 Process Management

 Files and Directories

 Users and Groups

 File Systems

 Installing CentOS

 SELinux - Security-Enhanced Linux

 Network Connection on CentOS

 Software Package Manager on CentOS - DNF and YUM

 Running Apache Web Server (httpd) on Linux Systems

 Running PHP Scripts on Linux Systems

Running MySQL Database Server on Linux Systems

 Install MySQL Database Server on CentOS

 Manage MySQL Server 'mysqld' on CentOS

 Set MySQL Server "root" Password on CentOS

 MySQL Server File Locations on CentOS

 MySQL Server Data Backups on CentOS

 MySQL Server Log Files on CentOS

 MySQL Server Socket Connection on CentOS

 MySQL Server TCP/IP Connection on CentOS

 Open Firewall for MySQL Server on CentOS

 "Server sent charset unknown to the client" Error

 Performance of Inserting Integers to MySQL Database

Performance Comparison of Inserting Integers vs. Strings

 Running Python Scripts on Linux Systems

 vsftpd - Very Secure FTP Daemon

 Postfix - Mail Transport Agent (MTA)

 Dovecot - IMAP and POP3 Server

 Email Client Tools - Mail User Agents (MUA)

 GCC - C/C++ Compiler

 Conda - Environment and Package Manager

 Tools and Utilities

 References

 Full Version in PDF/EPUB