query optimization of a left join
Often times slow queries are caused by a lack of index on a column that is being used in a where or join clause.

Try indexing all of the following:


u.user_id
f.user_id
f.main
u.mode
u.activated


You could also try moving f.main='Y' to the WHERE condition - I believe it will result in the same outcome.