Yes, I had this problem. First, in my opinion, if you have so many records, you should consider migrating to Mongo or a more powerfull DB like Postgres. I solved this problem by using raw sql and Phalcon\Paginator\Adapter\NativeArray as paginator. My cuurent limit is 10K rows and it is quite fast. So, for example:
class Products extends \Phalcon\Mvc\Model
{
// ... code
}
I would do it like this:
public function getProducts()
{
$sql = "SELECT * FROM products LIMIT 100000";
$product = new Product();
$results = new \Phalcon\Mvc\Model\Resultset\Simple(
null,
$product,
$product->getReadConnection()->query($sql)
);
if ($results->count() > 0) {
foreach ($results as $product) {
if (1 == $product->getIsActive()) {
$output[] = $product->toArray();
}
}
$paginator = new \Phalcon\Paginator\Adapter\NativeArray(
[
'data' => $output,
'limit' => 10,
'page' => 1 // $current_page or some other var
]
);
$result = $paginator->getPaginate();
return $result;
}
}