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

Phalcon's model search with paginator can easily paginate the results of query data, but if there are too many data, the speed will be too slow.

Code and problems generated by dev tool

The search-related volt files and controller files originally generated by phalcon-devtool will probably look like this

 <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>
        {% end for %}
        {% 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();
}

It can be found that in searchAction(), because Model::find($parameter) is done, and $parameter does not limit the range of the number of returned data, so if there are tens of thousands of eligible data, it may take only time for data transmission. eat a lot.

Solution

The solution is to add offset and limit to $parameter (in fact, you can also add order and other data, you can refer to the official phalcon document ), so that the data sent back can be limited to the number of strokes required for page display. Then get the number of transactions through Model::count($parameter) to calculate the total number of pages and the current number of pages and other things that the original Paginator will help to calculate.

 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 = [];
	}
	
	//Add order, limit, offset parameters. limit is equal to the number of data entries per page.
	$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;
	}

	//Remove offset, limit, order, call count() to calculate the number of all data, this function call actually executes 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 //At present, the number of pages will be calculated by yourself ]);

	$this->view->page = $paginator->getPaginate();
	
	//Calculate the number of all pages, the current page and other information $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