The isolated inner query is different in two important ways:
* it does not have
force index (letter_receiver_id_receiver_del_is_read_id_desc)
* it calls for "SELECT *", which requires it to fetch fields other than what is in the index.
What percentage of the rows satisfy
where receiver_id = 3 and receiver_del = 0
? If it is more than 10-30%, then the optimizer will decide to do a table scan rather than bouncing between the index and the data.
SHOW TABLE STATUS LIKE 'letter'\G
SHOW CREATE TABLE letter \G
Recommend you remove all uses of USE/FORCE INDEX().
This may be the optimal:
select id, date, sender_name, title, is_read, sender_id
from letter
inner join
( SELECT id
from letter i
where receiver_id = 3
and receiver_del = 0
order by is_read ,
id desc -- Needed for dups in 'is_read'?
limit 0, 15
) jt using(id)
order by is_read , id desc
The subquery will be "Using index"; the outer one will do 15 probes into the table, plus a using temporary and filesort, but this is not critical for 15 rows. And, if you have not TEXT fields, it might do it using a MEMORY table, hence, entirely in RAM.