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.