Forums

Core Scrapbook Display indexing slow queries preventing Old Page Version Deletes

Permalink
Believe I have found the problem I am having with deleting old page versions.

Spent 4 hours deleting a 100 old page versions and found that hundreds of slow queries were logged on the host involving the indexing of Core Scrapbook Display.

Anyone have an idea how I can fix??? The logs only occur when deleting old page versions.



# Thu May 31 05:44:22 2012
# Query_time: 1.057155 Lock_time: 0.000142 Rows_sent: 0 Rows_examined: 239345
use globams9_cnc6;
SET timestamp=1338464662;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '564')
MrGrowBizz
 
asshol1 replied on at Permalink Reply
here is the quick fix

@concrete\models\block.php#refreshCache()
$rows=$db->getAll('
            SELECT cvb.cID, cvb.cvID, cvb.arHandle 
            FROM 
               CollectionVersionBlocks cvb inner join btCoreScrapbookDisplay bcsd on cvb.bID = bcsd.bID
            WHERE bcsd.bOriginalID = ? ', array($this->getBlockID()));


using index bID would be faster than index PRIMARY
so, using join rather than sub query
will result in significant performance increase.

sorry for my poor english.