I have an InnoDB table called
articles_read with fewer than 20,000 rows in it. It consists of 3 columns:
member_id article_id timestamp
There are two indexes:
A unique index on (member_id, article_id) and a regular index on (timestamp).
It is read from fairly more than it is written to, and most of the times it's read from are as part of complex queries, but it is not one of our most active tables.
My dilemma is that the following query tends to take anywhere from 3-5 full seconds to complete, making it stand out in our slow query log by a factor of ten.
INSERT INTO articles_read SET member_id = 314062, article_id = 507257 ON DUPLICATE KEY UPDATE timestamp = NOW()