Is 20K reads going to make INSERTing into my table slow? How about a foreign key failure?

Question

It's taking 10 - 11 seconds sometimes to INSERT into my table which has under a dozen records.

I'm getting the following in my database's InnoDB status:

0.29 inserts/s, 0.03 updates/s, 0.09 deletes/s, 20365.73 reads/s. 20.37K reads/s

Seems like a lot--do you agree? I'm not sure the 20K reads is all the same table, but it's quite possible.

I also get

Foreign key constraint fails for table record_lock.

Could the foreign key constraint failure slow things down?

Here is the schema

CREATE TABLE IF NOT EXISTS `record_lock` (
  `module_id` int(10) unsigned NOT NULL,
  `module_record_id` int(10) unsigned NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `expiration_date_time` datetime NOT NULL,
  `date_time_created` datetime DEFAULT NULL,
  PRIMARY KEY (`module_id`,`module_record_id`),
  KEY `record_lock_site_id` (`site_id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

Much of the insert speed depends on how you're sending the data to the server to be inserted. If you are just listing out one insert statement after the next insert statement for thousands of lines then it will take a few seconds to parse and process that. It also takes time to transmit that over the network. I don' tknow if that is factoring into your timing.

The keys probably aren't making much of a difference if I had to guess.

You should probably be using the LOAD DATA INFILE funcionality of MySQL to insert a lot of records at time quickly. I've used this and it cuts down the insert performance time down from 10 - 30 seconds to less than a second.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 2

Use lock table and perfom innodb_buffer_pool_size.

Look at http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

This is allows to ignore checking of foreign key constraints for InnoDB tables:

SET FOREIGN_KEY_CHECKS = 0;

SET FOREIGN_KEY_CHECKS = 1;