MySQL query taking too long

[Dani]

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()

[pty]

So two things are happening here, first you're searching for a row and if you find one, you're updating the timestamp (and if not, doing an insert). It's unlikely that it's the insert that's slow, so let's check the query first:

explain select *
from articles_read
where member_id = 314062
and article_id = 507257;

The output should indicate whether or not your index is being used. If it's not, you need to look at why. If it is, then it might indiacate that the update (or insert) is slow. I suspet it's the former, so try the above and if that's not the problem we can look in more depth at the inserting/updating

[Dani]

I'm super duper sorry ... I forgot to mention that the problem is not reproducable, and only happens periodically, seemingly randomly from my perspective, but enough that it's a concern. I guess my question is whether a complex query that is reading from the table (and perhaps taking 1-3 seconds to complete) would delay writing this single row to the table? Doesn't InnoDB support row-level locking?

[rubberman]

Good question! I am not enough an expert with InnoDB to say whether or not it supports row-level locking on reads, or does by default. I know that MySQL itself has a ton of configuration options to set that can impact this. In any case, check into that. If your reads don't require repeatable reads (updates by other processes on some of the same rows), then row-level locking on reads should be disabled, otherwise the update operations will be stalled until the entire read operation(s) are complete and committed.

[Dani]

And pty, yes, the index is being used.

[Dani]

So I did a little bit of research and, if I'm understanding what I read correctly, INSERTs have a higher priority than SELECTs, which would invalidate my theory that the insert was waiting on selects. And for INSERTs to have a lower priority than the selects, you would do INSERT LOW_PRIORITY. Am I understanding this correctly?? Still isn't helping though :(

Would a REPLACE INTO be any better? I know there are differences between them. Just a thought.

[Dani]

Nope, didn't work. This just took 8.11 seconds:

REPLACE INTO articles_read
SET
    member_id = 377908,
    article_id = 507358

It should be noted that the timestamp column is set to CURRENT_TIMESTAMP by default. It takes milliseconds anytime I try to rerun it.

[pty]

Have you tried explaining the slow statement?

This might work:

explain insert into articles_read
set
    member_id = 314062,
    article_id = 507257
on duplicate key update
  timestamp = now();

Otherwise, try show profiles. It might give a hint as to why it's slow.