I've got a buffing trouble with an InnoDB table which i cannot determine:

  • I've got a table with 5 posts, which 4 posts from the primary key and you will find 3 more secondary secrets for the reason that table bulk card inserts take very very long time Up to 400 sec for 100,000 records (that's just 250 records/sec)
  • If all secondary indexes are dropped, the card inserts are extremely fast - 20 sec for 100,000 records (5000 records/sec or 20 occasions faster)
  • Basically add back even one secondary key, on one bigint user-id column also is an element of the primary key (I frequently need to find the information of specific customers), the card inserts immediately become 3-4 occasions reduced.

What's going on here? I suspect the reply is in the manner InnoDB stores indexes, but after lots of reading through, I can not decipher it. What's strangest of would be that the same table, with the indexes added, with MyISAM engine always works at 5000 records/sec.

I'm not sure why. However the MySQL website supplies a performance tuning tip for placing into InnoDB with secondary secrets (which suggests in my experience that case the actual way it is and also you suffer from it): "If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session..."