View Issue Details

IDProjectCategoryView StatusLast Update
0003402OXID eShop (all versions)4.05. Performancepublic2012-12-10 13:29
Status resolvedResolutionfixed 
Product Version4.5.4 revision 39463 
Target VersionFixed in Version4.6.0_beta3 
Summary0003402: Wrong join in oxarticlelist->loadCrossSell
DescriptionIn loadCrossSell the shop uses the following query:

        $sSelect = "select $sArticleTable.* from oxobject2article left join $sArticleTable on oxobject2article.oxobjectid=$sArticleTable.oxid ";
        $sSelect .= "where oxobject2article.oxarticlenid = $sArticleId ";
        $sSelect .= " and $sArticleTable.oxid is not null and " .$oBaseObject->getSqlActiveSnippet(). " order by rand()";

The left join is unefficient, because it takes "null" results from the left table (which are the majority of results in this situation) and filters them in the where clause with "oxarticle is not null".

An "inner join" would be much more efficient, because it filters the null results right when connecting the two tables. In fact, a left join is a inner join which does additional work by getting the null results, therefore, there is no reason to use a left join here.

Same with the query for bidirectional crossselling.
PHP Versionany
Database Versionany


Linas Kukulskis

2011-12-21 09:36

reporter   ~0005519

updated query