MySQL Forums :: Performance :: Long execution time despite LIMIT 3

SELECTs on InnoDB rarely cause replication to lag. The following can cause lags:
* Any long-running write query (UPDATE, ALTER, etc)
* A burst of quick write queries from multiple connections. (Replication is single-threaded, so they get queued up.)
* Slave hardware that is slower than the Master.
* SELECT(s) on the Slave that do enough locking of rows, together with write queries that are hitting the same rows or the "gaps" next to the rows.
* Long running transactions. (Long time between BEGIN and COMMIT.)

The first two items sound somewhat like your comment:
> Other queries on other tables that take a long time never cause the slaves to lag.

Meanwhile...
Let's carry the optimization another step:

SELECT  m.*, m.flags + 0 flags
    FROM messages_test m
    JOIN
        (
        (
        SELECT id
            FROM  messages_test
            WHERE  (thread = 817549.001266066)
              AND  (id_user_s = 817549)
              AND  (del_s = 0)
            ORDER BY id DESC LIMIT 3
        )
        UNION DISTINCT
        ( SELECT  id
            FROM  messages_test
            WHERE  (thread = 817549.001266066)
              AND  (id_user_r = 817549)
              AND  (del_r = 0)
           order by  id desc  LIMIT  3
        )
        ) x  ON x.id = m.id
    order by  id desc  LIMIT  3;

Note how the the inner SELECTs and the UNION only haul around `id`. This is much less bulky. Then, the outer query does the heavy lifting of reaching into messages_test (again) to get the rest of the fields.

Also, note that the inner SELECTs will be performed entirely in INDEXes. See EXPLAIN SELECT ... -- it will say "Using index".