We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Paginator

Counting query takes a lot of time in QueryBuilder

I have quite big data set in my database (it is about 2M rows). According a documentation, its better to use a QueryBuilder adapter for paginating a large datasets. Create and configuration of this type of paginator was smooth and easy. As I suspected this type of configuration will generate 2 queries. First query will be rows gathering, and second one will count how many rows meets a requirements. As I have nothing to complain on gathering query, counting query is very inefficient and lasts forever. Table is optimized for sure and I have proper indexes.

Let me show my problem on examples:

Simple count query:

SELECT COUNT(*) FROM points WHERE queue = 2 AND accepted = 1

took 0.019 sec

Phalcon generated query:

SELECT COUNT() "rowcount" FROM (SELECT points. FROM points WHERE (points.queue = 2) AND (points.accepted = 1)) AS T

took 6.4659 sec

Is there any possibility to change this query, cache it or at least put number of pages / items as an option to constructor?



3.5k
edited Mar '15

Query builder generates Phql syntax, i think that whether you use phql syntax is fast. You could make a test and show us



2.0k
edited Mar '15

Thanks Julian for reply. Problem is that QueryBuilder Adapter generate this query by himself, and I don't know how I can override this count() query. For now code looks like this:

...
$builder = $this->modelsManager->createBuilder()
    ->from('Points')
    ->where('queue = 2')
    ->andWhere('accepted = 1')
    ->orderBy('index_created DESC');

$pictures = new Pager(
   new Paginator\Adapter\QueryBuilder(array(
      'builder' => $builder,
      'limit'   => 7,
      'page'    => $page
   )),
   array(
      'layoutClass' => 'Phalcon\Paginator\Pager\Layout\Bootstrap',
      'rangeClass'  => 'Phalcon\Paginator\Pager\Range\ReverseSliding',
      'rangeLength' => 10,
      'urlMask'     => $this->url->get(array(
         'for'   => 'home-page-page',
         'page'  => '{%page_number}'
      ))
   )
);
...


3.5k

What if you try:


        $phql = "SELECT
                          count(*)
                      FROM
                         Points
                      WHERE 
                        Points.queue = 2 AND 
                        Points.accepted = 1
                      ORDER BY 
                        Points.index_created DESC
        ";
        $builder = $this->modelsManager->->createQuery($phql);
        $builder->execute();