3

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.

kernelpanic
  • 1,276
  • 1
  • 10
  • 30
user257938
  • 31
  • 1
  • 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

1 Answers1

0

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.

igelkott
  • 233
  • 3
  • 8