Paginator using so much resource

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 ?



4.3k
edited Jul '15

You're not using the right adapter to fit your needs: https://docs.phalconphp.com/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.

edited Jul '15

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
    ));


4.3k

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.phalconphp.com/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?