Goal: 

The goal of test is retrieve all articles tagged by one or several tags. So we doesn’t need the list of tags for each articles, but we need the conditions on the HABTM Tag model. We want to see articles list paginated with 30-50 records on each page. There is two possibility for searching articles my specific tags. 1) Use internal cakePHP search with condition to hasbtm model:         

$this->unbindAll(array(‘hasAndBelongsToMany’ => array(‘Tag’))); 
$this->hasAndBelongsToMany['Tag']['conditions'] = $this->cond ; 
$result=$this->findAll( array(‘Article.id’ => range(200,250))); 

In this case executed two query ([2], [3]). First used for conditions on Article model, and based on it cake build second query [3] condition. 2) Use my extension of dbo_mysql driver that posted at bakery: http://bakery.cakephp.org/articles/view/extending-of-dbosource-and-model-with-sql-generator-function         

$this->unbindAll();        

$this->ArticlesTag->unbindAll(array(‘belongsTo’ => array(‘Tag’)));
$q=$this->ArticlesTag->getQuery($this->cond,‘article_id’); 
$result=$this->findAll(array(  array( ‘Article.id’ => range(200,250),  ‘id’ => “IN -!(”. $q .“)”),));  

In this case buld only one query to DB [1]. This query is combination of [2] and [3]. 

Nr Query Affected Num. rows Took (ms)
1 SELECT `Article`.`id`, `Article`.`num`, `Article`.`subject`, `Article`.`body` FROM `articles` AS `Article` WHERE (`Article`.`id` IN (200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250) ) AND (`id` IN (SELECT `ArticlesTag`.`article_id` FROM `articles_tags` AS `ArticlesTag` LEFT JOIN `tags` AS `Tag` ON (`ArticlesTag`.`tag_id` = `Tag`.`id`) WHERE ((`Tag`.`tag` like ‘text%’) OR (`tag` like ‘%msg%’)) ))   51 51 2
2 SELECT `Article`.`id`, `Article`.`num`, `Article`.`subject`, `Article`.`body` FROM `articles` AS `Article` WHERE `Article`.`id` IN (200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250) 51 51 1
3 SELECT `Tag`.`id`, `Tag`.`num`, `Tag`.`tag`, `ArticlesTag`.`tag_id`, `ArticlesTag`.`article_id` FROM `tags` AS `Tag` JOIN `articles_tags` AS `ArticlesTag` ON (`ArticlesTag`.`article_id` IN (200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250) AND `ArticlesTag`.`tag_id` = `Tag`.`id`) WHERE ((`Tag`.`tag` like ‘text%’) OR (`tag` like ‘%msg%’)) 290 290 5

   

Time for both query is comparable (6 ms for first test, and 2ms for second). Average 10 test runs show next resultsnative method: 0.2427011013031dbo_mysql_ext method: 0.026184034347534 As we analyze before mysql does not take many time so most time lost in data analyzation in case of cake core method. In first variant cake spend time for placing hasbtm records to parents. As I mention before we doesn’t need the list of tags for each articles. 

Conclussion: 

So we see that the extension of dbo driver give good perfomance (about ten times) in comparisson cake core method. 

Run benchmark: 

 1. Download file http://cakeexplorersamples.googlecode.com/files/bench.zip 

2. Start ‘cake schema create’ 

3. Populate test data by calling /articles/generate 

4. Run test by calling /articles/bench