# 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.

• 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.