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".