Simple UPDATE query takes too much time in InnoDB than MyISAM

[Atul Patil]

Quick description:

In my application (VC++/Windows 8) I am issuing simple update queries to increase value of a field in a InnoDB table. It takes way too long than the same with MyISAM table.


Details:

I am creating a table DEMO_TABLE having columns MyID and MyCounter (both integers) and engine MyISAM

pStatement->execute("CREATE TABLE IF NOT EXISTS DEMO_TABLE(MyID int NOT NULL PRIMARY KEY, MyCounter int) ENGINE=MyISAM");

I then added to the table a row having MyID value equal to 0. Then I am issuing UPDATE query in a loop:

time_t dwTime1 = time(&dwTime1);
for (int i=0; i<500; i++)
{
char strUpdateRequest[256];
sprintf_s(strUpdateRequest, 256, "UPDATE DEMO_TABLE SET MyCounter = (MyCounter + 1) WHERE ThreadID = 0");
pStatement->executeUpdate(strUpdateRequest);
}
time_t dwTime2 = time(&dwTime2);
std::cout << "\nTime difference: " << (dwTime2 - dwTime1);

It ran quickly and the output was:

Time difference: 0

Means it has consumed less than a second.

But when I deleted table and repeated all this exercise again with InnoDB engine.

pStatement->execute("CREATE TABLE IF NOT EXISTS DEMO_TABLE(ThreadID int NOT NULL PRIMARY KEY, MyCounter int) ENGINE=InnoDB");

However, this time to my surprise it took much longer and the output was:

Time difference: 17

It has consumed 17 seconds.

(Despite, in both the cases above I checked table containts and found MyCounter column value has been populated correctly (500))

UPDATE:

I also have observed there is lot of disk activity in these 17 seconds.

Question:

In many discussions and even in MySQL documents it has been mentioned that in case of updates InnoDB performs much better than MyISAM. But I observed exactly opposite.

Can someone please clarify this behavior? Am I doing anything wrong?