View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0004857OXID eShop (all versions)4.05. Performancepublic2013-01-16 16:212013-01-29 13:14
Reportersinkacom Assigned To 
PrioritynormalSeveritytweakReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.5.11 revision 46050 
Fixed in Version4.7.4 / 5.0.4 revision 57063 
Summary0004857: Cross-Selling Statement Optimization
DescriptionWhen the loadArticleCrossSell function is called (usually details page) and Cross-Selling is activated, the statement produced by the function creates a lot of database load.

It would be probably better to create a statement using
1. The "UNION" operator instead of 2 x "LEFT JOIN" and an "WHERE ( O2A2.oxarticlenid IS NOT NULL OR O2A1.oxobjectid IS NOT NULL )"
2. Remove the order by rand()
3. Try to use "INNER JOIN", as it might use some key instead of full table scans.

Numbers 2 and 3 are also valid vor unidirectional cross selling.
Steps To ReproduceHave Cross Selling active
Have an article table of about 25.000 articles (17.000 Main Articles, some of them with Variants)
Additional InformationThis was one of the final performance problems which only now surfaced after the article table has been reverted from MyISAM to InnoDB. We had 1,300 users, taking about 5 minutes per visit, in the hour following a rather large newsletter.
It went smoothly for over 2 months before that, including the same kind of mailings every two weeks.

The Bug was seen on 4.5.11 PE (the shop in question is running this), but it has not been significantly changed at all through to 4.7.2.
http://docu.oxid-esales.com/CE/sourcecodedocumentation/4.7.2.53018/oxarticlelist_8php_source.html#l00329
TagsProducts
Attached Files
database_query_explained.png (57,520 bytes)   
database_query_explained.png (57,520 bytes)   
ThemeBoth
BrowserAll
PHP Versionany
Database Versionany

Relationships

parent of 0004704 resolvedLinas Kukulskis WIth 6700 articles, the query of crossselling can heavy slow down the server 

Activities

sinkacom

2013-01-28 14:53

reporter   ~0008337

The attached file database_query_explained.png shows what the database has to work through.

The resulting rows (4 at most), are in no relation to the vast amount of lines created "on the fly".

The resulting table (before cutting it down) would result at around 4 * 189 * 54382 = 41,112,792 rows. The statement above has the limit part of "LIMIT 0,4" already appended. This means that 41,112,788 created rows are discarded.

I just let the statement run without EXPLAIN. It took 11.9136 seconds.

Is there any official fix for the 4.5 branch?

Linas Kukulskis

2013-01-29 13:14

reporter   ~0008346

changed query to:



 if ( $myConfig->getConfigParam( 'blBidirectCross' ) ) {
            $sSelect = "
                (
                    SELECT $sArticleTable.* FROM $sArticleTable
                        INNER JOIN oxobject2article AS O2A1 on
                            ( O2A1.oxobjectid = $sArticleTable.oxid AND O2A1.oxarticlenid = $sArticleId )
                    WHERE 1
                    AND " . $oBaseObject->getSqlActiveSnippet() . "
                    AND ($sArticleTable.oxid != $sArticleId)
                )
                UNION
                (
                    SELECT $sArticleTable.* FROM $sArticleTable
                        INNER JOIN oxobject2article AS O2A2 ON
                            ( O2A2.oxarticlenid = $sArticleTable.oxid AND O2A2.oxobjectid = $sArticleId )
                    WHERE 1
                    AND " . $oBaseObject->getSqlActiveSnippet() . "
                    AND ($sArticleTable.oxid != $sArticleId)
                )";
        }