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.

Improve this question
2
  • 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. Commented Aug 13, 2011 at 21:02
  • My database is on my local machine (without any load) and I'm the only one person to use it. Commented Aug 13, 2011 at 21:28

1 Answer 1

Reset to default
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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.