Paginator using so much resource

Question

Paginator using so much resource

HACKHERS Jul '15

Created Jul '15	Last Reply Jul '15	Replies 2	Views 621	Votes 0

HACKHERS 1.2k

Jul '15

Hey, I see bunch of discussions about that but still cant figure out why phalcon gets all data for paginator. Here is my code:

        $eklenen = Test::find(array(
            "durum = 1 $sorguDevam",    
            "order" => "tarih DESC",
            "cache"      =>  array("key" => "SonEklenen_".$this->utils->kategori."_Limitless", "lifetime" => $this->utils->eklenenLimitsiz),
        ));

        $paginator   = new PaginatorModel(
            array(
                "data"  => $eklenen,
                "limit" => 20,
                "page"  => $sayfa
            )
        );

In this one, cache gets all the matching results. Isn't it waste of resource. I get faster results with this one than QueryBuilder. QueryBuilder is getting all data too. In 100k rows in database that will be a big problem isnt it ?

Steven · Answer 1 · 2015-07-25T20:27:19-07:00

You're not using the right adapter to fit your needs: https://docs.phalcon.io/en/latest/reference/pagination.html#adapters-usage

  <?php
  ...
  use Phalcon\Paginator\Adapter\QueryBuilder as PaginatorQueryBuilder;
  ...
  // Passing a querybuilder as data

  $builder = $this->modelsManager->createBuilder()
      ->columns('id, name')
      ->from('Robots')
      ->orderBy('name');

  $paginator = new PaginatorQueryBuilder(array(
      "builder" => $builder,
      "limit"   => 20,
      "page"    => 1
  ));

The query builder will only query the data you need for that specific request.

HACKHERS · Answer 2 · 2015-07-26T03:24:49-07:00

Still, It's getting all results of my matching results. Isn't it better to count all and select page from database? If I use this in every single page phalcon will get all results from database that matching with my terms. In model pagination i get 33ms with cache but with builder i get 100ms load time

Edit: I just added 100k rows. And Query builder taking like 800ms to paginate. How can I cache it. It's not cool to wait like 1 sec for every page imagine 1k users refreshing it in same time..

My code:

    $builder = $this->modelsManager->createBuilder()
        ->where('durum = 1 '.$sorguDevam)
        ->columns('id, baslik, sef_link, resim, izlenme')
        ->from('Test')
        ->orderBy('tarih DESC');

    $paginator = new Phalcon\Paginator\Adapter\QueryBuilder(array(
        "builder" => $builder,
        "limit"=> 20,
        "page" => $sayfa
    ));

Steven · Answer 3 · 2015-07-26T11:14:35-07:00

I have a table with over 5 million rows and it takes less than 10ms to return a resultset of 20 results. It sounds like you're having a query performance issue, not a Phalcon/Pagination issue.

Try profiling your query using the query profiler: https://docs.phalcon.io/en/latest/reference/models.html#profiling-sql-statements

I also see that you're sorting your resultset. Is there a composite index on both columns durum, tarih?