View Issue Details

IDProjectCategoryView StatusLast Update
0007167OXID eShop (all versions)4.09. SEO, SEO URLpublic2020-08-26 15:49
Reporterkaistrecker 
PriorityhighSeveritymajorReproducibilityalways
Status acknowledgedResolutionopen 
Product Version6.2.2 
Target VersionFixed in Version 
Summary0007167: The second database query in SeoEncoderCategory->markRelatedAsExpired is really slow, if there are to many relations
DescriptionThe second query sets the expired flag for all seo urls of all subarticles. The article ids are loaded with a subquery. But the more category relations an article has, the more often its id is included in the result of the subquery. In our case, we get about 300.000 article ids, with only about 14.000 unique article ids. The update query multiplicates that number too, because the oxseo records are updated multiple times. So instead of about 2.000.000 oxseo records, it updates about 42.000.000 oxseo records.

But there is a simple solution for this problem. Just change the subquery from "(select o2c.oxobjectid [...]" to "(select distinct o2c.oxobjectid [...]".

By doing this, every article id is only loaded once and thus the related oxseo records are only updated once, too.

https://github.com/OXID-eSales/oxideshop_ce/blob/master/source/Application/Model/SeoEncoderCategory.php#L214
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionNot defined

Activities

QA

2020-08-18 13:49

administrator   ~0013276

-MK