View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0003296OXID eShop (all versions)4.05. Performancepublic2011-10-07 10:122012-12-10 13:29
Reporterwanis Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionfixed 
Product VersionPast development 
Fixed in Version4.5.9 revision 43186 
Summary0003296: using SQL_CALC_FOUND_ROWS instead of adodb::recordCount() in oxarticlelist::loadCategoryArticles()
Descriptionoxarticlelist::loadCategoryArticles line around 421:
...
        $sSelect = $this->_getCategorySelect( $sArticleFields, $sCatId, $aSessionFilter );

        // calc count - we can not use count($this) here as we might have paging enabled
        // #1970C - if any filters are used, we can not use cached category article count
        $iArticleCount = null;
        if ( $aSessionFilter) {
            $oRet = oxDb::getDb()->Execute( $sSelect );
            $iArticleCount = $oRet->recordCount();
        }

        if ($iLimit = (int) $iLimit) {
            $sSelect .= " LIMIT $iLimit";
        }

        $this->selectString( $sSelect );
...

articles are counted by selecting SQL and viewing mysql_num_rows(). this causes MySQL to fill PHP buffer and do other unneded performance/memory not friendly stuff.

My suggestion is to use SQL_CALC_FOUND_ROWS flag, and getting record count by SELECT FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html#id797568
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

please also check if SQL_CALC_FOUND_ROWS will use mysql query cache. as if not, check/compare which is better PHP-MySQL buffer size or MySQL query cache

Additional Informationcode can be rewriten to:
...
        $sSelect = $this->_getCategorySelect( $sArticleFields, $sCatId, $aSessionFilter );

        if ( $aSessionFilter) {
            $sSelect = str_ireplace('select', 'select SQL_CALC_FOUND_ROWS', $sSelect, 1);
        }

        if ($iLimit = (int) $iLimit) {
            $sSelect .= " LIMIT $iLimit";
        }

        $this->selectString( $sSelect );

        if ( $aSessionFilter) {
            return oxDb::getDb()->GetOne('SELECT FOUND_ROWS();');
        }
...
TagsPerformance
ThemeBoth
BrowserAll
PHP Versionany
Database Versionany

Activities

Linas Kukulskis

2012-03-12 12:52

reporter   ~0005949

fixed, calculation made by additinal query with Count(*) and removed all sortings etc. in most cases it is fastest solution.

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
http://www.phpdevblog.net/2009/06/mysql-pagination-sql-calc-found-rows-vs-count-query.html