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.

  • You say that you're the only one doing test on this table but what about the rest of the database/server? Seems like this may have something to do with server load and database engine load. – murisonc Aug 13 '11 at 21:02
  • My database is on my local machine (without any load) and I'm the only one person to use it. – user257938 Aug 13 '11 at 21:28

For InnoDB, the "auto_increment" variable should be in an index (first or alone) to avoid some lock issues. This doesn't happen with MyISAM, as far as I understand.

By the way, why isn't "Id" the primary key? Not that it's necessary but it just "looks" like it should be for this table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.