JDBC Tutorials - Herong's Tutorial Examples - v3.12, by Dr. Herong Yang
InnoDB (MySQL 5.5 Default Engine) Slower on INSERT
This section provides a tutorial example showing that InnoDB, the MySQL 5.5 storage engine, is about 1,000 times slower than MyISAM storage engine.
To find out why MySQL 5.5 and higher is so slow to insert records comparing to MySQL 5.0 I searched the Internet and found the answer. By default, MySQL 5.5 creates table using the InnoDB storage engine, while MySQL 5.0 uses the MyISAM engine. So the performance difference is mainly due to the difference of the storage engine.
Let's take a look at the current storage engine of the table, drop the table and create the table again with the MyISAM engine:
herong> mysql -u root mysql> use HerongDB; Database changed mysql> show table status; | Name | Engine | Version | Row_format |... | profile | InnoDB | 10 | Dynamic |... mysql> drop table profile; mysql> create table profile ( -> ID INTEGER PRIMARY KEY AUTO_INCREMENT, -> FirstName VARCHAR(20) NOT NULL, -> LastName VARCHAR(20), -> Point REAL DEFAULT 0.0, -> BirthDate DATE DEFAULT '2000-12-31', -> ModTime TIMESTAMP DEFAULT '2026-12-31 23:59:59.999') -> ENGINE=MyISAM; mysql> show table status; | Name | Engine | Version | Row_format |... | profile | MyISAM | 10 | Dynamic |...
Now re-run MySqlPerformancePreparedStatement.java a macOS system with a 2.6 GHz processor, running JDK 13, MySQL 8.0.17 and MySQL Connector/J 8.0.19:
PreparedStatement insert 10000 rows with 3314 milliseconds
The total execution time dropped from 4373 milliseconds to 3314 milliseconds. So MyISAM is slightly faster than InnoDB for simple inserting record operations.
As a reference, here is the result on a Windows 7 system with a 2.5GHz processor, running JDK 1.8, MySQL 5.5 and MySQL Connector/J 5.1.36:
PreparedStatement insert 10000 rows with 879 milliseconds
The total execution time dropped from 945810 milliseconds to 879 milliseconds. So MyISAM is about 1,000 times faster than InnoDB for simple inserting record operations.
Here is summary of performance results on this program in the past with different versions of storage engine, MySQL, Java, and JDBC driver:
Operation: PreparedStatement for INSERT Execution Storage MySQL Java JDBC Computer CPU Time (ms) Engine Server Version Driver System GHz --------- ------- ------ ------- ------ ------- --- 3314 MyISAM 8.0 13 8.0 macOS 10 2.6 4373 InnoDB 8.0 13 8.0 macOS 10 2.6 879 MyISAM 5.5 8 5.1 Windows 7 2.5 945810 InnoDB 5.5 8 5.1 Windows 7 2.5 1281 MyISAM 5.0 6 5.0 Windows XP 1.0
As you can see, MySQL and JDBC driver 8.0 is much slower than MySQL and JDBC driver 5.5. Why? Maybe macOS 10 is slower than Windows 7?
Table of Contents
JDBC (Java Database Connectivity) Introduction
Installing and Running Java DB - Derby
Derby (Java DB) JDBC DataSource Objects
Java DB (Derby) - DML Statements
Java DB (Derby) - ResultSet Objects of Queries
Java DB (Derby) - PreparedStatement
MySQL JDBC Driver (MySQL Connector/J)
PreparedStatement with Parameters
PreparedStatement in Batch Mode
Performance of Inserting Rows with a PreparedStatement
►InnoDB (MySQL 5.5 Default Engine) Slower on INSERT
Performance of Inserting Rows with a Regular Statement
Performance of Inserting Rows with a ResultSet
MySQL - Reference Implementation of JdbcRowSet
MySQL - JBDC CallableStatement
MySQL CLOB (Character Large Object) - TEXT
MySQL BLOB (Binary Large Object) - BLOB
Oracle Express Edition Installation on Windows
Oracle - Reference Implementation of JdbcRowSet
Oracle - JBDC CallableStatement
Oracle CLOB (Character Large Object) - TEXT
Oracle BLOB (Binary Large Object) - BLOB
Microsoft SQL Server Express Edition
Microsoft JDBC Driver for SQL Server
Microsoft JDBC Driver - Query Statements and Result Sets
Microsoft JDBC Driver - DatabaseMetaData Object
Microsoft JDBC Driver - DDL Statements
Microsoft JDBC Driver - DML Statements
SQL Server - PreparedStatement
SQL Server CLOB (Character Large Object) - TEXT
SQL Server BLOB (Binary Large Object) - BLOB
JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver
JDBC-ODBC Bridge Driver - Flat Text Files
JDBC-ODBC Bridge Driver - MS Access
JDBC-ODBC Bridge Driver - MS SQL Server
Summary of JDBC Drivers and Database Servers