EAV, multiple left join query optimisation
Please explain the order by - limit - group by.  It sounds kludgy and possibly illegal in future versions.
Store the tail of the filename so you can avoid SUBSTRING (etc) in critical places like WHERE clauses.
Show us SHOW CREATE TABLE.