title: The usage of Doctrine Cache [useResultCache function] + Dql Leftjoin produce more queries
body: <p>I working on speeding up my  Zendframework application  + Doctrine 1.2 with the use of :</p>
  
  <ol>
  <li>APC  </li>
  <li>Doctrine [useQueryCache function]</li>
  <li>Doctrine [useResultCache function]</li>
  </ol>
  
  <p>from the doctrine <a href="http://www.doctrine-project.org/documentation/manual/1_1/en/caching%3aquery-cache-&amp;-result-cache" rel="nofollow">documentation</a> : </p>
  
  <blockquote>
    <p>When using a result cache things get even better. Then your query
    process looks as follows (assuming a valid cache entry is found):</p>
    
    <p>Init new DQL query</p>
    
    <p>Return the result set</p>
  </blockquote>
  
  <p>Bootstrap.php : to enable APC caching  </p>
  
  <pre><code>if (isset($doctrineConfig['cache']) &amp;&amp; $doctrineConfig['cache'] == true) {
              $cacheDriver = new Doctrine_Cache_Apc();
              $manager-&gt;setAttribute(Doctrine_Core::ATTR_QUERY_CACHE, $cacheDriver);
              $manager-&gt;setAttribute(Doctrine_Core::ATTR_RESULT_CACHE, $cacheDriver);
              $manager-&gt;setAttribute(Doctrine_Core::ATTR_RESULT_CACHE_LIFESPAN, 1800);
          }
  </code></pre>
  
  <p>When i am adding <code>useResultCache("true")</code> it rising the query count from  11 to 18 queries 
  and the time elapsed raised up from <code>1.8 m.sec</code> to <code>14.82 m.sec</code> </p>
  
  <p>here is my dql query : </p>
  
  <pre><code>   $q-&gt;from("Dagho_Model_History h")
            -&gt;where("h.date BETWEEN ? AND ? ", array($start_of_day, $end_of_day))
            -&gt;leftJoin("h.Order o")
            -&gt;leftJoin("o.Personal p")
            -&gt;leftJoin("o.Domain d")
            -&gt;leftjoin("d.Tld t")
            -&gt;useResultCache(true)
            -&gt;useQueryCache(true);
  </code></pre>
  
  <p>It broke down the dql joining parts to be executed in separated sql , i will list the resulted sql in both cases : </p>
  
  <p>-before the use of <code>useResultCache(true)</code>
  <code>SELECT h.id AS h__id, h.order_id AS h__order_id, h.type AS h__type, h.date AS h__date, h.receipt AS h__receipt, h.dollar AS h__dollar, h.payment AS h__payment, h.profit AS h__profit, h.net_profit AS h__net_profit, h.exchange_diff AS h__exchange_diff, o.id AS o__id, o.domain_id AS o__domain_id, o.hosting_id AS o__hosting_id, o.personal_id AS o__personal_id, o.auth_id AS o__auth_id, o.timestamp AS o__timestamp, o.notes AS o__notes, o.price AS o__price, o.type AS o__type, o.expire_at AS o__expire_at, o.hosting_price AS o__hosting_price, o.domain_price AS o__domain_price, o.extra_fee AS o__extra_fee, o.orderid AS o__orderid, o.root_id AS o__root_id, o.lft AS o__lft, o.rgt AS o__rgt, o.level AS o__level, o.created_at AS o__created_at, p.id AS p__id, p.name AS p__name, p.sex AS p__sex, p.email AS p__email, p.landline AS p__landline, p.mobile AS p__mobile, p.address AS p__address, p.city AS p__city, p.national_number AS p__national_number, p.notes AS p__notes, p.credit AS p__credit, p.stopped AS p__stopped, p.root_id AS p__root_id, p.lft AS p__lft, p.rgt AS p__rgt, p.level AS p__level, p.created_at AS p__created_at, p.updated_at AS p__updated_at, d.id AS d__id, d.name AS d__name, d.sld AS d__sld, d.tld AS d__tld, d.personal_id AS d__personal_id, d.hosting_id AS d__hosting_id, d.tld_id AS d__tld_id, d.registartion AS d__registartion, d.expiration AS d__expiration, d.notes AS d__notes, d.status AS d__status, d.provider AS d__provider, d.domainnameid AS d__domainnameid, d.orderid AS d__orderid, d.os_username AS d__os_username, t.id AS t__id, t.tld AS t__tld, t.price AS t__price, t.dollar AS t__dollar FROM history h LEFT JOIN orders o ON h.order_id = o.id LEFT JOIN personal p ON o.personal_id = p.id LEFT JOIN domain d ON o.domain_id = d.id LEFT JOIN tld t ON d.tld_id = t.id WHERE (h.date BETWEEN ? AND ?)</code></p>
  
  <ul>
  <li>after the use of <code>useResultCache(true)</code></li>
  </ul>
  
  <p><code>SELECT p.id AS p__id, p.name AS p__name, p.sex AS p__sex, p.email AS p__email, p.landline AS p__landline, p.mobile AS p__mobile, p.address AS p__address, p.city AS p__city, p.national_number AS p__national_number, p.notes AS p__notes, p.credit AS p__credit, p.stopped AS p__stopped, p.root_id AS p__root_id, p.lft AS p__lft, p.rgt AS p__rgt, p.level AS p__level, p.created_at AS p__created_at, p.updated_at AS p__updated_at FROM personal p WHERE (p.id = ?)</code></p>
  
  <p>and </p>
  
  <p><code>SELECT d.id AS d__id, d.name AS d__name, d.sld AS d__sld, d.tld AS d__tld, d.personal_id AS d__personal_id, d.hosting_id AS d__hosting_id, d.tld_id AS d__tld_id, d.registartion AS d__registartion, d.expiration AS d__expiration, d.notes AS d__notes, d.status AS d__status, d.provider AS d__provider, d.domainnameid AS d__domainnameid, d.orderid AS d__orderid, d.os_username AS d__os_username FROM domain d WHERE (d.id = ?)</code></p>
  
  <p>i.e its create sql for <code>-&gt;leftJoin("o.Personal p")</code> and 
                         <code>-&gt;leftJoin("o.Domain d")</code></p>
  
  <p>but i can't understand why it doesn't create 
  <code>-&gt;leftjoin("d.Tld t")</code></p>
  
  <p>Can any one explain why its took this <code>14.82 sec</code> ??</p>