0

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.

enter image description here

5
  • 1) Does your subquery REALLY selects over 10kk records? if not then remove both ORDER BY and LIMIT. 2) What is the goal of grouping in outer query? You'll get one RANDOM record for each group with the same id value. PS. And where is EAV mentioned in subj?
    – Akina
    Mar 20 '20 at 8:42
  • Thank you for your input the EAV is tObjects, tObjectProperties, tObjectValues Mar 20 '20 at 10:11
  • I tried number 1 and it drop 8 seconds off the time!! :D amazing Mar 20 '20 at 10:14
  • I am wondering if anyone else has had to encounter bad EAV performance issues: EAV does not scale very well and I suspect that every one that uses it and have a sufficient amount of data experience performance problems.
    – Lennart
    Mar 20 '20 at 17:52
  • I am definitely in agreement there Lennart Mar 21 '20 at 12:44
0
  • 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.