Can I Cut Down On The Number of DB Queries CakePHP Is Making Here?

So I have a Cake query that looks something like this:

$forthcomingReleases = $this->Release->find('all', array(
        'contain'=>array('Artist.name', 'Artist.slug', 'Releasetype.type', 'Format.Mediatype.name'),
        'conditions'=>array('release_date >' => date('Ymd'), 'Release.is_deleted' => false),                
        'order'=>array('release_date DESC'),
        'limit'=>开发者_运维问答10
    ));

And what I'm seeing in the SQL dump is this sort of thing:

45  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10021      1   1   167
46  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10159      1   1   168
47  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10021      1   1   170
48  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10159      1   1   168
49  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 1338       1   1   169
50  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10159      1   1   187
51  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 569        1   1   211
52  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 569        1   1   168
53  SELECT `Artist`.`name`, `Artist`.`slug` FROM `artists` AS `Artist` WHERE `Artist`.`id` = 10451      1   1   182
54  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 901         1   1   170
55  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 901         1   1   171
56  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 901         1   1   180 
57  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 900         1   1   171
58  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 900         1   1   183
59  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 901         1   1   171
60  SELECT `Releasetype`.`type` FROM `releasetypes` AS `Releasetype` WHERE `Releasetype`.`id` = 901 

(This is just an excerpt for illustration purposes, there are lots more queries.)

Even if each of these queries is only taking <200 ms, it still seems they might be adding up to something significant, and this is especially annoying when so many of the queries are duplicates - e.g. all the Releasetype.types being either 900 or 901.

Is there any way I can refactor my query, my model relationships, or something else so that the data is retrieved in considerably fewer calls?

Cake is doing one 'main' query. "Release" model find in this case, and for each row that is found there its doing a query to get the data you have specified in 'contain'.

This is expected behavior.

A big problem I see is your statement 'these queries is only taking <200 ms'. Imo they should not be taking more than 1ms or even 0ms. It would seem you need to index that table properly.

I am not particularly fond of forcing cake to do joins, as if it was meant to be a join the cake devs would have made it join.

If you really want to lower the query count even though its not needed, look at a) linkable behavior b) bindModel c) adhoc-joins

As an alternative you might re-arrange the query into something like this:

$releaseList = $this->Release->find('list', array(
     'conditions' => array(
        'release_date > ' => date('Ymd'), 
        'Release.is_deleted' => false
     ),
     'fields' => array('id','id')

));
$releaseIds = array_keys($releaseList);

//now the actual query you used, searching by ids should execute faster
$forthcomingReleases = $this->Release->find('all', array(
   'conditions' => array('Release.id' => $releaseIds),
   'contain'    => array('Artist.name', 'Artist.slug', 'Releasetype.type', 'Format.Mediatype.name'),
    'order'     => array('release_date DESC'),
    'limit'     => 10 
));