Queries like this are pretty common in our slow query logs:
228826-# User@Host: fetlife[fetlife] @ app4.dal.fetlife [10.18.5.79]
228827-# Thread_id: 42698 Schema: fetlife_production
228828-# Query_time: 7.656149 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1 Rows_read: 2
228829:INSERT INTO friendship_requests (user_id, requested_by_user_id, created_at, updated_at) VALUES (?, ?, ‘2012-02-21 03:37:50’, ‘2012-02-21 03:37:50’);
This table has been behaving funny for a while. Almost no lock time, only 2 rows read, 1 affected, and almost 8 seconds of runtime.
CREATE TABLE friendship_requests (
id int(11) NOT NULL auto_increment,
user_id int(11) NOT NULL,
requested_by_user_id int(11) NOT NULL,
created_at datetime default NULL,
updated_at datetime default NULL,
PRIMARY KEY (id),
UNIQUE KEY index_friendship_requests_on_user_id_and_requested_by_user_ id (user_id,requested_by_user_id),
KEY index_friendship_requests_on_user_id_and_created_at (user_id,created_at)
) ENGINE=InnoDB AUTO_INCREMENT=13940603 DEFAULT CHARSET=utf8
and
±---------+
| count(*) |
±---------+
| 983848 |
±---------+
No other table in our system has this issue.
Ideas?