[Phalcon] Retrieve only records of the current page in paginator to improve performance

Phalcon 的 model search 配合 paginator 就可以很方便地幫查詢資料結果做分頁，但是如果資料筆數過多，則會有速度過慢的問題。

dev tool 產生的程式碼與問題

原本用 phalcon-devtool 產生的 search 相關 volt 檔案與 controller 檔案大概會長這樣

<div class="row">
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>Field A</th>
                <th>Field B</th>
                <th>Field C</th>

                <th></th>
                <th></th>
            </tr>
        </thead>
        <tbody>
        {% if page.items is defined %}
        {% for modelobjs in page.items %}
            <tr>
                <td>{{ modelobjs.fielda}}</td>
                <td>{{ modelobjs.fieldb}}</td>
                <td>{{ modelobjs.fieldc}}</td>

                <td>{{ link_to("modelobjs/edit/"~modelobjs.fielda, "Edit") }}</td>
                <td>{{ link_to("modelobjs/delete/"~modelobjs.fielda, "Delete", "onclick":"return confirm('Are you sure?')") }}</td>
            </tr>
        {% endfor %}
        {% endif %}
        </tbody>
    </table>
</div>

<div class="row">
    <div class="col-sm-1">
        <p class="pagination" style="line-height: 1.42857;padding: 6px 12px;">
            {{ page.current~"/"~page.total_pages }}
        </p>
    </div>
    <div class="col-sm-8">
        <nav>
            <ul class="pagination">
                <li>{{ link_to("modelobjs/search", "First") }}</li>
                <li>{{ link_to("modelobjs/search?page="~page.before, "Previous") }}</li>
                <li>{{ link_to("modelobjs/search?page="~page.next, "Next") }}</li>
                <li>{{ link_to("modelobjs/search?page="~page.last, "Last") }}</li>
            </ul>
        </nav>
    </div>
    <div class="col-sm-3">
        <p class="pagination" style="line-height: 1.42857;padding: 6px 12px;">
            Total count: {% if page.items is defined %}
                {{ page.total_items }}
            {%endif%}
        </p>
    </div>
</div>

public function searchAction()
{
  $numberPage = 1;
  if ($this->request->isPost()) {
    $query = Criteria::fromInput($this->di, 'Modelobj', $_POST);
    $this->persistent->parameters = $query->getParams();
  } else {
    $numberPage = $this->request->getQuery("page", "int");
  }

  $parameters = $this->persistent->parameters;
  if (!is_array($parameters)) {
    $parameters = [];
  }
  $parameters["order"] = "eventtypeid";

  $modelobjs = Modelobj::find($parameters);
  if (count($modelobjs ) == 0) {
    $this->flash->notice("The search did not find any modelobjs");

    $this->dispatcher->forward([
      "controller" => "modelobjs",
      "action" => "index"
    ]);

    return;
  }

  $paginator = new Paginator([
    'data' => $modelobjs,
    'limit'=> 10,
    'page' => $numberPage
  ]);

  $this->view->page = $paginator->getPaginate();
}

可以發現searchAction()中，因為做了 Model::find($parameter)，且 $parameter 沒有限制回傳資料筆數區間，所以如果符合條件的資料有上萬筆，那可能光資料傳送的時間就吃掉許多。

解決方式

解決的方式就是在 $parameter 中加入 offset 與 limit (實際上還可以加入 order 等資料，可以參考 phalcon 官方文件)，這樣就可以限縮回傳的資料剛好是頁面顯示需要的筆數。然後透過 Model::count($parameter) 取得筆數，來計算全部頁數以及目前頁數等原先 Paginator 會幫忙計算的東西。

public function searchAction()
{
	$numberPage = 1;
	if ($this->request->isPost()) {
		$query = Criteria::fromInput($this->di, 'Modelobj', $_POST);

		$this->persistent->parameters = $query->getParams();
	} else {
		$numberPage = $this->request->getQuery("page", "int");
	}

	$parameters = $this->persistent->parameters;
	if (!is_array($parameters)) {
		$parameters = [];
	}
	
	//加入 order, limit, offset 參數。limit 就等於是每頁資料筆數。
	$parameters["order"] = "fielda, fieldb";
	$parameters["limit"] = "10";
	$parameters["offset"] = max(0,($numberPage - 1)) * 10;

	$modelobjs = Modelobj::find($parameters);

	if (count($modelobjs) == 0) {
		$this->flash->notice("The search did not find any modelobjs");

		$this->dispatcher->forward([
			"controller" => "modelobjs",
			"action" => "index"
		]);

		return;
	}

	//拿掉 offset, limit, order ，呼叫 count() 來計算全部資料筆數，這個 function call 實際上就是執行 select count(*)
	unset($parameters["offset"]);
	unset($parameters["limit"]);
	unset($parameters["order"]);

	$modelobj_count = Modelobj::count($parameters);

	$paginator = new Paginator([
		'data' => $eventlog,
		'limit'=> 10,
		// 'page' => $numberPage	//目前在第幾頁就變成要自己計算
	]);

	$this->view->page = $paginator->getPaginate();
	
	//計算全部頁數，目前在第幾頁等資訊
	$total_pages = ceil($modelobj_count/10);

	$this->view->page->total_pages = $total_pages;
	$this->view->page->last = $total_pages;
	$this->view->page->next = min($total_pages, $numberPage + 1);
	$this->view->page->before = max(1, $numberPage - 1);
	$this->view->page->total_items = $eventlog_count;
	$this->view->page->current = max(0, ($numberPage - 1)) + 1;
}

Original link: Phanix's Blog