0

MySql 5.6, InnoDB. I have a table with 8 million records. It has an integer primary key, and a unique index u1 CHAR(40). It also has a few small fields and a VARCHAR(1000).

SELECT COUNT(*) FROM my_table FORCE INDEX(u1) takes 1.0 s

SELECT COUNT(*) FROM my_table FORCE INDEX(PRIMARY) takes 1.2 s

This is on Linux boxen. But on Windows boxen the times are 2 s and 45 s!

The cnf/ini files are roughly the same. Tweaking buffer sizes etc has been unsuccessful so far. There seems to be a big problem with primary key lookups on Windows. Perhaps a bug in InnoDB on Windows? Or what?

Improve this question
2
  • I presume the hardware is roughly equivalent?
    – Colin 't Hart
    Commented Oct 1, 2015 at 13:51
  • 2
    Lots and lots of room for differences here. Does EXPLAIN give different results between the two servers? Does SHOW VARIABLES report any differences in version, innodb_version, or optimizer_switch? Do the two servers have substantially different storage architecture or I/O loads?
    – db2
    Commented Oct 1, 2015 at 14:25

1 Answer 1

Reset to default
2

It turns out that the difference was the size of innodb_buffer_pool_size. On the Win box it was too low.

It appears that, if you need the primary key, all the records (8 million times 1000 chars) need to be read. InnoDB seems to use the primary key as a "clustered index", meaning that it's welded to each record. That's why reading just the values of the secondary key takes so much less memory, and thus less use of the memory buffer. If it's too small, the read will take a long time.

Increasing innodb_buffer_pool_size to 8G helped speed up operations.

I certainly don't understand the minute details, please correct me if this is nonsense.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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