title: EAV, multiple left join query optimisation
body: <p>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? </p>
  
  <pre><code> 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`
  </code></pre>
  
  <p>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. </p>
  
  <p><a href="https://i.stack.imgur.com/isTVl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/isTVl.png" alt="enter image description here"></a></p>