Very slow INSERT queries through C/C++ MySQL API
Posted by:
M D
Date: June 08, 2011 10:51AM
Hello,
I have written a small C aplication for parsing the txt files and inserting the data from them to MySQL database. I checked the program using only one file (around 3500 lines) and it was executing for almost 3 minutes! So my first thought was that, the programs is not optimize very well when it comes to parsing the txt file, so I decided to write very simple program which will insert the same data (not from the txt file) 3500 times and compare the time of the execution. Below I present the idea:
----------------------------------------
// PROGRAM
// init of mysql is here + the connection to mysql
// I use always the same connection, I do not connect
// and disconnect everytime
//
int i;
for(i =0; i < 3486; i++)
{
mysql_query(mysql_connection, "INSERT INTO `table` VALUES(20110608,1234.0,1234.0,1234.0,1234.0,1234)");
}
----------------------------------------
// TABLE STRUCTURE
CREATE TABLE `tabela` IF NOT EXISTS
(
column_1 DATE DEFAULT NULL,
column_2 FLOAT(5,2) DEFAULT NULL,
column_3 FLOAT(5,2) DEFAULT NULL,
column_4 FLOAT(5,2) DEFAULT NULL,
column_5 FLOAT(5,2) DEFAULT NULL,
column_6 FLOAT(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
----------------------------------------
The program above also executed for almost 3 minutes! Why inserting the data is soooo slow? Does any of you had similar problem? Maybe I need to tune my.conf? I appreciate your help, regards MD