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.
Best Answer
SUBSTRING
(etc) in critical places likeWHERE
clauses.SHOW CREATE TABLE
.