Linux Apps Tutorials - Herong's Tutorial Examples - v1.03, by Herong Yang
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) 2009 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
Running Apache HTTP Server (httpd) on Linux Systems
Running Apache Tomcat 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
Conda - Environment and Package Manager