Very slow loading data when using paginator

As far as optimization goes, since Model::find returns every rowset, you would probably be better off with a QueryBuilder. Since PDO doesn’t support scrollable cursors this adapter shouldn’t be used to paginate a large number of records

I assume your example is in a Model context:

$builder = $this->getModelsManager()->createBuilder()
   ->addFrom(__CLASS__)
   ->where($params['conditions'], $params['bind']);
   ->orderBy($params['order'])
   ->groupBy($params['group']);

$this->pagination = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
   'builder'  => $builder,
   'limit' => $itemsPerPage,
   'page'  => $page
));

It's worst now it got 23s to load 12 next items, for example:

in car table is 1884 items

in cars table ( view with joined arguments and params ) is 1 070 840 but after group that is 1884, its due to many parameters, which cars has, so searching happen here.

But when i load all items with simple select:

SELECT * FROM `cars` WHERE is_active = 1 AND expiration > '2015-08-13 00:00:00'

so time to do this query is 0,0250s

when i try:

SELECT * FROM `cars` WHERE is_active = 1 AND expiration > '2015-08-13 00:00:00' GROUP BY id LIMIT 12 OFFSET 2

it's cost just 0,0200s so problem is not in db scheme.

Hmm... I have a base table of cities with 3million records which is then inner joined and filtered with where getting the final result count to around 100 000 sometimes. QueryBuilder works fine for me (1-2s max), have you tried logging or debugging your SQL queries that the application makes? If those 0.02s measures come from raw SQL queries, they don't really mean much, because more things can happen in a live application. I once made the mistake of having a heavy query in the Controller initialization and dispatcher forwarding within the same Controller... so it got run twice.

I find this tool really helpful: Snowair Phalcon Debugbar

The problem is the fact that ALL the rows are being retrieved before being paginated, like @Lajos said. If you use the QueryBuilder, it will only retrieve the necessary rows.

I used the query builder no it's worst solution it takes a 20s to load but when i try to make any query with that table and filter it's cost just 0,02s max in raw query from mysql.

Now its:

            $params = $this->buildRequest();

            $builder = $this->getModelsManager()->createBuilder()
                ->addFrom(get_called_class())
                ->where($params['conditions'], $params['bind'])
                ->orderBy($params['order'])
                ->groupBy($params['group']);

             $this->pagination = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
                'builder'  => $builder,
                'limit' => $itemsPerPage,
                'page'  => $page
             ));

             return $this->pagination->getPaginate()->items;

and it's wors that before, now it cost 23s, before 9s, raw query 0,02s, ist not about query, i have to build largest project than this, with more row than this and it's no about table scheme;.

Well QueryBuilder is actually the only method to go with large number of results... I understand that it takes longer in your case, but it should definetly not be the case. As I've said my base recordset is over 3 million with joins and conditions, and it performs well.

So have you overriden any factory class? What is the parent class of the actual Model? Are there any hasMany sub-tables that you query for each item? Phalcon does much more than a simple raw SQL... I strongly suggest you to log the queries that the application makes, it can reveal the flaws.

I agree with what @Lajos said - QueryBuilder is the way to go and should absolutely not be giving you the times you're seeing. Something else is the problem. Logging your queries will probably give you the best idea what the problem actually is.

Another possibilty: I don't know the exact layout of your class, but if you use the pagination on instance level (based on $this->paginate), it could be that you're doing an iterated pagination for each record...

Look at this, i turn off all hasMany, hasOne relationships and i make simple query logger here is all queries which runs:

[before: 20:03:16] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'visitor' AND `TABLE_SCHEMA` = DATABASE() 
[after: 20:03:16] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'visitor' AND `TABLE_SCHEMA` = DATABASE() 

[before: 20:03:16] DESCRIBE `visitor` 
[after: 20:03:16] DESCRIBE `visitor` 

[before: 20:03:16] SELECT COUNT(42) AS `rowcount` FROM `visitor` WHERE `visitor`.`visitor_id` = :vid AND `visitor`.`created_at` > :lastDay 55cc9cc5086c0,2015-08-12 23:59:59
[after: 20:03:16] SELECT COUNT(42) AS `rowcount` FROM `visitor` WHERE `visitor`.`visitor_id` = :vid AND `visitor`.`created_at` > :lastDay 55cc9cc5086c0,2015-08-12 23:59:59

[before: 20:03:16] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'cars' AND `TABLE_SCHEMA` = DATABASE() 
[after: 20:03:16] SELECT IF(COUNT(*) > 0, 1, 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'cars' AND `TABLE_SCHEMA` = DATABASE() 

[before: 20:03:16] DESCRIBE `cars` 
[after: 20:03:17] DESCRIBE `cars` 

[before: 20:03:17] SELECT `cars`.`id`, `cars`.`user_id`, `cars`.`manufacturer_id`, `cars`.`model_id`, `cars`.`title`, `cars`.`description`, `cars`.`transmission`, `cars`.`price`, `cars`.`color`, `cars`.`bulk`, `cars`.`fuel`, `cars`.`doors`, `cars`.`places`, `cars`.`state`, `cars`.`performance`, `cars`.`body`, `cars`.`vintage`, `cars`.`milage`, `cars`.`owners`, `cars`.`vin`, `cars`.`mark`, `cars`.`stk`, `cars`.`service_list`, `cars`.`type`, `cars`.`drive`, `cars`.`main_image`, `cars`.`top`, `cars`.`highlight`, `cars`.`expiration`, `cars`.`visits`, `cars`.`url`, `cars`.`created_at`, `cars`.`updated_at`, `cars`.`is_leasing`, `cars`.`is_prooved`, `cars`.`is_dph`, `cars`.`is_active`, `cars`.`fn_data`, `cars`.`security`, `cars`.`exterior`, `cars`.`interior` FROM `cars` WHERE 'A' = 'A' AND `cars`.`is_active` = :is_active0 AND `cars`.`expiration` > :expiration1 GROUP BY `cars`.`id` ORDER BY `cars`.`top` DESC, `cars`.`id` DESC LIMIT :AP0 OFFSET :AP1 1,2015-08-13 20:03:16,12,12
[after: 20:03:18] SELECT `cars`.`id`, `cars`.`user_id`, `cars`.`manufacturer_id`, `cars`.`model_id`, `cars`.`title`, `cars`.`description`, `cars`.`transmission`, `cars`.`price`, `cars`.`color`, `cars`.`bulk`, `cars`.`fuel`, `cars`.`doors`, `cars`.`places`, `cars`.`state`, `cars`.`performance`, `cars`.`body`, `cars`.`vintage`, `cars`.`milage`, `cars`.`owners`, `cars`.`vin`, `cars`.`mark`, `cars`.`stk`, `cars`.`service_list`, `cars`.`type`, `cars`.`drive`, `cars`.`main_image`, `cars`.`top`, `cars`.`highlight`, `cars`.`expiration`, `cars`.`visits`, `cars`.`url`, `cars`.`created_at`, `cars`.`updated_at`, `cars`.`is_leasing`, `cars`.`is_prooved`, `cars`.`is_dph`, `cars`.`is_active`, `cars`.`fn_data`, `cars`.`security`, `cars`.`exterior`, `cars`.`interior` FROM `cars` WHERE 'A' = 'A' AND `cars`.`is_active` = :is_active0 AND `cars`.`expiration` > :expiration1 GROUP BY `cars`.`id` ORDER BY `cars`.`top` DESC, `cars`.`id` DESC LIMIT :AP0 OFFSET :AP1 1,2015-08-13 20:03:16,12,12

[before: 20:03:18] SELECT COUNT(*) AS `rowcount` FROM `cars` WHERE 'A' = 'A' AND `cars`.`is_active` = :is_active0 AND `cars`.`expiration` > :expiration1 GROUP BY `cars`.`id` 1,2015-08-13 20:03:16
[after: 20:03:19] SELECT COUNT(*) AS `rowcount` FROM `cars` WHERE 'A' = 'A' AND `cars`.`is_active` = :is_active0 AND `cars`.`expiration` > :expiration1 GROUP BY `cars`.`id` 1,2015-08-13 20:03:16

The queries is before run and after run ( to catch time ) And about relation ship its lazy it cannot be called at the same time as query, to load.

Soo the execution time of the generated query by the builder is ~1 sec. That kind of drives our point home. You're doing something else in your code that results in the unacceptable execution times.