EAV, multiple left join query optimisation

Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. What is the best way to increase the speed on this?

SELECT * FROM
(
    SELECT f.*, p.timestamp
    FROM tObjectValues ov
    INNER JOIN tObjectProperties op ON ov.propertyId = op.id
    INNER JOIN tObjects o ON op.objectId = o.id
    INNER JOIN files f ON SUBSTRING(ov.value, CHAR_LENGTH('cache://content//')) = f.filename
    LEFT JOIN publishFiles pf ON f.id = pf.fileId
    LEFT JOIN publishes p ON p.id = pf.publishId AND o.appId = p.appId AND p.type = 'l'
    WHERE o.status = 1 AND op.status = 1 AND ov.status = 1 AND ov.value LIKE 'cache://content/%' AND o.appId = 2
    ORDER BY p.timestamp DESC
    LIMIT 0, 10000000
) s
GROUP BY s.id

I have run the explain function to delve further into the issue however i was wondering if anyone who has had similar problem could help in the optimisation.