Paginating large amounts of data

It looks like paginator fetches all 150k items to array ... and it fails.

Maybe you need to open an issue. I suspected that the paginator works in that way, but i had no time to check. Paginator must retrieve only the data we want to display...

Resultsets doesn't return all the records from the mapped table, it just return the required set by the paginator. The problem here is moving the cursor to the specified position. Since the PDO adapters don't support scrollable cursors Phalcon does need to move the cursor manually and this is slow.

I recommend you use a paginator like the one used in this forum that uses a count and a query with LIMIT/OFFSET.

https://stackoverflow.com/questions/12044636/php-pdo-mysql-scrollable-cursor-doesnt-work

Well, that is bummer! :(

Or go plain old good mysql way: SQL_CALC_FOUND_ROWS / FOUND_ROWS(). But consider that it does full table scan which can be slow. But outputting pager with 7500 elements ... seriously?!

Well, Phalcon could do something like Zend Paginator does. Zend Paginator uses the query we've built, changes it to perform a row count and the applies the limit/offset to the original query.

@roman-kulish: I'm not outputting a pager with 7500 elements! What do you mean with that?

Sad... but we need to implement own paginator ... like Zend_Paginator

We're working on a new paginator that uses a query builder instead of a resulset. However that would solve the problem in a bit more cases than scrollable cursors, if there are many rows, MySQL needs to perform a full scan in the server side to seek the required starting row.

https://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ https://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down https://stackoverflow.com/questions/12680738/mysql-query-with-limit-and-large-offset-taking-forever https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

The unique real way to get an optimized and fast paginator is having an ordered sequential numeric column to seek rows without an offset clause using an index

Nice. A paginator using a query builder would be better for most cases. Will it be available on Phalcon 1.0.0?

I'm not sure if it could be ready for 1.0.0 but we could add it to the incubator, however check the links I'm refering, I'm not sure if a limit/offset is a solution since you have a 150k rows table

Handling it in query builder can be a good idea. I am not sure how you want to implement it on a database level, cause of specific database features you use to estimate total number of rows. I hope you will not want treat PDO as a cursor this time and will make it optimized?

@Phalcon have you considering moving paginator to UI level, e.g., Volt extension or Tag? Having specific paginator for each data source increases granularity of the codebase. On the other hand paginator is a simple piece of code which just needs to know total number of items, current page and desired items limit.

Wow, it's great! Thanks!!!

Great.... when we can use 1.1.0 ? Will you try to implement nestet sets - Modified Pre-order Tree Traversal in Phalcon in 1.1, or 1.2 ? please .......

Nice! :) When will 1.1.0 be available?

1.1.0 needs a bit more of work, we need to fix some more bugs, and complete the Assets component: https://docs.phalcon.io/en/1.1.0/reference/assets.html

thanks for the enthusiasm!

I use it, it's so cool, please add this to invo or vokuro:

example in invo:

$parameters = array();
        if ($this->persistent->searchParams) {
            $parameters = $this->persistent->searchParams;
        }

        $productTypes = ProductTypes::find($parameters);
        if (count($productTypes) == 0) {
            $this->flash->notice("The search did not find any product types");
            return $this->forward("producttypes/index");
        }

        $paginator = new Phalcon\Paginator\Adapter\Model(array(
            "data" => $productTypes,
            "limit" => 10,
            "page" => $numberPage
        ));
        $page = $paginator->getPaginate();

        $this->view->setVar("page", $page);
        $this->view->setVar("productTypes", $productTypes);

It's too slow with big table.

Thanks.

My solution :

https://github.com/pejman-hkh/phalcon-big-table-pagination-model-solution