Insert (write) performance

Question

We are trying to speed up the queries in our application and in the process identified all queries which took more than 100ms. We noticed that there were few insert statements which took more than 200ms. For example we have an insert to the following table which is consistently taking more than 200ms. Is there some way of increasing the performance of the insert? Also there is another table called message_Details which has a foreign key relation with this table.

CREATE TABLE `message` ( 
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `subject` varchar(255) DEFAULT NULL,
       `message` mediumtext,
       `message_type` int(3) NOT NULL DEFAULT '0',
       `posted_on` datetime DEFAULT NULL,
       `posted_by` bigint(20) NOT NULL DEFAULT '0',
       `posted_to` bigint(20) NOT NULL DEFAULT '0',
       PRIMARY KEY (`id`),
       KEY `posted_by` (`posted_by`),
       KEY `posted_to` (`posted_to`)
     ) ENGINE=InnoDB AUTO_INCREMENT=305845 DEFAULT CHARSET=latin1        

Any advice is greatly appreciated as I am not a database guy.

Answer 1

Table structure-wise, you are not doing anything wrong that would slow down inserts:

• You (presumably) insert in PRIMARY KEY order via the auto_increment value.
• There are few secondary indexes on the table. Both posted_by and posted_to are non-unique, which means InnoDB can use the insert buffer.

I would suggest looking at either configuration or hardware. I would agree that 200ms is a lot, so it looks like there has to be some contention somewhere.