Advice on fine-tuning query and index for performance
The query seems ok (although the LIMIT 1 inside the NOT EXISTS subquery is not needed) but you are probably missing some index. I suggest:


add a composite index on log_follow (id, other_id):

ALTER TABLE log_follow
  ADD INDEX id_other_id_idx
    (id, other_id) ;

an index on darkhast_follower (status, id_request) might help as well but with such a small table, probably not much.
the datatype of the various id columns (id, other_id, id_request) are varchar(20) with charset UTF8. If you only have ASCII characters there, it would be better to use Latin charset. This will lower the column width from 60 (20x3) bytes to 20, and thus reduce the index size, both in disk and memory. If you only store integers in these columns, it would be even better to use BIGINT, reducing the size to 8 bytes.