The "derived" table (the subquery) has 1176 rows. Subqueries have no indexes. The subquery is handled last, as if there were no practical way to start with it. Hence, it hand to scan all 1176 rows repeatedly.
for reference:
SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN
( SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND ( x.eve =0 OR x.eve =2 )
GROUP BY `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL(x.see, 0) + f.lee + IFNULL(x.lee, 0) > 0
ORDER BY f.added DESC
> Edit Drop bhash BTREE No No bhash (20) 27815 A
Does that mean that you have a "prefix" index? That may be a mistake.
What is the datatype of bhash?
What is the datatype of anno.hash?
Please provide SHOW CREATE TABLE (for each table).
Do you need LEFT JOIN? Won't JOIN do provide the same answer, at least for tabel2?
Do you need all 27819 rows?
One approach is to create a temp table with the subquery, and index it on hash.