Optimize MySQL Database Insertion and Updating

Additional steps can be taken to optimize the insertion or update of data within a MySQL database.

Optimize MySQL Database Insert

To speed up data insertion, you need to append ?useServerPrepStmts=false&rewriteBatchedStatements=true to the URL property that you specify in the JDBC Config File.

e.g. url=jdbc:mysql://localhost:<portNumber>/<dbName>

?useServerPrepStmts=false&rewriteBatchedStatements=true

Optimize MySQL Database Update

In order to make the data updation faster, you need to make 2 changes in your MySQL configuration file (mysqld.cnf) under [mysqld] section.
[mysqld]

key_buffer_size = 1024
innodb_flush_log_at_trx_commit = 2

Note: To learn more about the innodb_flush_log_at_trx_commit property, click here. 

Steps to follow on Mac/Linux to make changes in mysqld.cnf file

• Open the mysqld.cnf file in any text editor. You can also use the vim editor to open the file: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
• Search for the key_buffer_size option and update its value to 1024M
• Search for the innodb_flush_log_at_trx_commit option and update its value to 2
  Note: If the innodb_flush_log_at_trx_commit option is not in the file, please add this entry and assign the value to it.
  
   
• After making the changes mentioned above, you need to restart the MySQL service: sudo service mysql restart

Steps to follow on Windows to make changes in mysqld.cnf file

• On Windows, the file my.cnf might be called my.ini. MySQL looks for it in the following locations (in the order mentioned below):
  • %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini or, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf
  • %WINDIR%\my.ini, %WINDIR%\my.cnf
  • C:\my.ini, C:\my.cnf
  • INSTALLDIR\my.ini, INSTALLDIR\my.cnf
    
    
     
• Open my.cnf or my.ini file and, under [mysqld] section,
  • Search for the key_buffer_size option and update its value to 1024M
  • Search for the innodb_flush_log_at_trx_commit option and update its value to 2

Note: If the innodb_flush_log_at_trx_commit option is not in the file, please add this entry and assign the value to it.

• After making the above changes, open the command prompt or PowerShell with administrator privilege and stop/start the MySQL service:
  net stop MySQL57
  net start MySQL57