I'm doing some tests with a really simple InnoDB table (named Test) with the following structure:
Id int(10) unsigned NOT NULL AUTO_INCREMENT UserId int(10) NOT NULL Body varchar(512) COLLATE utf8_unicode_ci NOT NULL CreatedAt datetime NOT NULL
one additional index on UserId:
KEY Idx_Test_UserId (UserId) USING BTREE
When I try to execute this query...
INSERT INTO Comments (UserId,Body,CreatedAt) VALUES (1,'This is a test',NOW())
...sometimes I get the operation completed in a few milliseconds but some other times it takes around a second.
I have the same issue with a simple SELECT statement:
SELECT Body FROM Comments WHERE UserId=1
I'm the only one person doing the tests on this specific table, I really don't understand I have such execution time differences.
Last note, when I'm doing the same tests with a MyISAM table I don't have any issues.