Mysql – Insert (write) performance

insertMySQLperformance

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.

Best Answer

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.

Related Question