View Issue Details

IDProjectCategoryView StatusLast Update
0004704OXID eShop (all versions)4.05. Performancepublic2012-12-10 13:29
Reportermichael_keiluweit Assigned To 
Status resolvedResolutionfixed 
Product Version4.7.0 / 5.0.0 revision 51243 
Fixed in Version4.7.1 / 5.0.1 revision 52468 
Summary0004704: WIth 6700 articles, the query of crossselling can heavy slow down the server
DescriptionThe query (application\models\oxarticlelist.php loadArticleCrossSell()):

        // 0000525 bidirectional crossselling
        if ( $myConfig->getConfigParam( 'blBidirectCross' ) ) {
            $sSelect = "select distinct $sArticleTable.* from oxobject2article left join $sArticleTable on (oxobject2article.oxobjectid=$sArticleTable.oxid or oxobject2article.oxarticlenid=$sArticleTable.oxid) ";
            $sSelect .= "where (oxobject2article.oxarticlenid = $sArticleId or oxobject2article.oxobjectid = $sArticleId )";
            $sSelect .= " and $sArticleTable.oxid is not null and " .$oBaseObject->getSqlActiveSnippet(). " having $sArticleTable.oxid!=$sArticleId order by rand()";

causes a server lag if many articles are selected.
Because only X articles are really needed ( $this->setSqlLimit( 0, $myConfig->getConfigParam( 'iNrofCrossellArticles' ));) it is maybe an overload to select all possible articles, rand them and cut the result with the parameter iNrofCrossellArticles.
PHP Versionany
Database Versionany


child of 0004857 resolvedLinas Kukulskis Cross-Selling Statement Optimization 


Linas Kukulskis

2012-11-14 08:25

reporter   ~0007849

Reminder sent to: michael_keiluweit

can you attach dump of this db: at least this two tables: oxarticles and oxobject2article?

Linas Kukulskis

2012-11-15 13:23

reporter   ~0007861

optimized query