title: Yii2 optimizing long running queries to lower timeouts
body: <p>Am trying to fetch over 10000 records with a query but keep on getting timeouts</p>
  
  <pre><code>   $from ="2018-06-20 23:59";
      $to ="2018-06-28 00:00";
      //generate nairobi plant excell data
      $query = NaiTblTrucks::find()
          -&gt;leftJoin('tbl_truck_history', 'tbl_truck_history.truck_id = tbl_trucks.id')
          //-&gt;where(['tbl_trucks.town' =&gt; 1])
          -&gt;leftJoin('tbl_security_trucks', 'tbl_security_trucks.truck_id = tbl_trucks.id')
          -&gt;andWhere(['IN', 'tbl_truck_history.status', [1]])
          -&gt;andWhere(['NOT IN', 'tbl_trucks.truck_status', [13,6]])
          -&gt;orderBy(['tbl_security_trucks.time_out' =&gt; SORT_DESC]);
  
      $query-&gt;andFilterWhere([
          'OR',
          ['IN',  'tbl_trucks.material',3]
      ]);
  
      $query-&gt;andWhere(['between', 'tbl_security_trucks.time_out', strtotime($from), strtotime($to)]);
  </code></pre>
  
  <p>Now am using the dataprovider to return results, like</p>
  
  <pre><code> $data = new ActiveDataProvider([
          'query' =&gt; $query,
          'sort' =&gt; ['defaultOrder' =&gt; ['created_at' =&gt; SORT_DESC]],
          'pagination' =&gt;false
      ]);
  </code></pre>
  
  <p>From the dates above from and to the data expected can be very large, that is a day can have over 1000 records.
  I have also tried paginating the requests by</p>
  
  <pre><code>    $totalrecords = $data-&gt;getTotals()//dataprovider get totals
      $gotcount = 0;
      $currentpage = 0;
      $fetched = [];
  
      for ($val= 0; $val&lt;=$totalrecords;){
          $fetchedata = $this-&gt;getTrucks($query,10,$currentpage);
          array_push($fetched,$fetchedata);
          $gotcount += count($fetchedata);
          $val +=10;
          $currentpage += 1;
      }
  
   public function getTrucks($query, $pagesize, $currentPage){
      $data = new ActiveDataProvider([
          'query' =&gt; $query,
          'sort' =&gt; ['defaultOrder' =&gt; ['created_at' =&gt; SORT_DESC]],
          'pagination' =&gt; [
              'pageSize' =&gt; 100,
              'page' =&gt; 0
          ],
      ]);
  
      return $data-&gt;getModels();
  
       }
  </code></pre>
  
  <p>but both the above tries still give me timeouts. By reducing the dates to a day am able to get the data.How do i optimize this further to ensure that i dont get timeouts. Are there better ways of doing this especially when handling large datasets.</p>
  
  <p>Later after fetching this data am exporting this to excell with php spreadsheets .</p>