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
pointsWHERE queue = 2 AND accepted = 1
took 0.019 sec
Phalcon generated query:
SELECT COUNT() "rowcount" FROM (SELECT points. FROM
queue= 2) AND (
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?