View Issue Details

IDProjectCategoryView StatusLast Update
0004704OXID eShop (all versions)4.05. Performancepublic2012-12-10 13:29
Reportermichael_keiluweit 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.7.0 / 5.0.0 revision 51243 
Target VersionFixed 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()):

-----------8<-----(snip)--------------
        // 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()";
        }
-----------8<-----(snip)--------------

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.
TagsPerformance
ThemeBoth
BrowserAll
PHP Versionany
MySQL Versionany

Relationships

child of 0004857 resolvedLinas Kukulskis Cross-Selling Statement Optimization 

Activities

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