Re: trouble optimizing query - taking much longer than expected

[Rick James]

> Weird, I responded to this some time ago and now the post is not here.

I see that sentence in the 8th post. You,you,me,you,me,you,me,SM.

> By default, however, it is capture_timestamp DESC and all dates - just as the original query I posted.

Well, nearly the original, you mean the OFFSET is initially 0?:

SELECT  u.username,
        c.computer_name,
        f.file_id, f.file_action, f.filename,
        f.capture_timestamp
    FROM  computers       AS c
    INNER JOIN  users     AS u  ON u.computer_id = c.computer_id
    INNER JOIN  file_logs AS f  ON f.user_id = u.user_id
      AND  f.capture_timestamp BETWEEN 0 AND 1415301792  -- This increases over time?
    WHERE  c.account_id = 20   -- This various with the 'user'?
      AND  c.status = 1
    ORDER BY  f.capture_timestamp DESC
    LIMIT  0, 100

What if you changed
AND f.capture_timestamp BETWEEN 0 AND 1415301792
to
AND f.capture_timestamp > UNIX_TIMESTAMP() - 86400

That would show up to 100 records in the last day. If it comes up empty, tell them there are none (or show an empty list). (Perhaps 1 day is not realistic. Suggest seeing how far back you would need to go for 5-10% of the file_log table, then use that amount instead of 86400.)

I am hoping that the EXPLAIN will reverse the order of tables, and start with file_logs. However, it would need
INDEX(capture_timestamp)

You are saying that a query like this is the most common one, so it needs to be efficient.
I'm saying that I don't know how to make it efficient, but perhaps this 'minor' modification could be efficient without messing with user expectations too much.

I am hoping the 5-10% suggestion will have it do a different, more efficient, EXPLAIN plan.