DDC-3282: Pagination class CountOutputWalker has poor performance with MySQL

[doctrinebot]

Jira issue originally created by user fredpeaks:

When the CountOutputWalker is used for pagination, it creates a count query of this type :

    SELECT %s AS dctrn_count
    FROM (
        SELECT DISTINCT "here are the identifiers from the original query"
        FROM (
            "here is the original query"
        ) dctrn_result
    ) dctrn_table

The problem is that the original query inside the count query is executed without limiting the results number each time the pagination has to count the total number of rows. And when the total number of rows returned by the original query is large and/or with big selected rows, it can hurt badly the performance, even kill the server.
Maybe I don't understand something but in my opinion this count query does exactly what we try to avoid with pagination : load all data at one time !
So I don't understand why things are done this way. Again, I'm not a db specialist so I'm almost sure I'm missing something.

But the thing is I've met these performance issues with big select queries on a medium amount of rows (~35000) on MySQL (using knp-components Pager).
The ugly solution I found was to use the CountWalker instead of the CountOutputWalker except when the query has a "HAVING" clause. That was because the CountWalker produce a better count query for performance but cannot handle well "HAVING" clauses (as far as I know).
Finally I think the solution is to modify the CountWalker so it can take care of more complex queries and/or improve the CountOutputWalker to preserve performance.

Here are some discussions related to this problem :
Removed use of CountOutputWalker
Count Performance of DoctrineORMAdapter COUNT query and large record sets
Doctrine ORM pagination improvements

Thanks a lot for your time :)

[doctrinebot]

Comment created by stof:

Well, the issue is that the CountWalker cannot count stuff using a GROUP BY or HAVING clause by design. It is simply impossible to write the given SQL without ending up on what the CountOutputWalker is doing,(maybe a bit simpler in some cases thanks to a complete knowledge of what the query is doing, but not much and not in a general case).

The solution is indeed to tell the Paginator not to use the output walker when you know it is not necessary. This is precisely why there are 2 implementations of the pagination with a boolean flag to switch between them

[doctrinebot]

Comment created by stof:

And the output walker actually perform better than the tree walker in many platforms according to [~beberlei](not MySQL though), which is why it is hard to choose the best walker for queries being supported by both of them (a project can do it better than the core, as it knows which platform it uses)

[doctrinebot]

Comment created by fredpeaks:

Thank you for your very clear explanations !
I assumed I was missing something but I did not think it was just impossible to solve this problem "automatically" . That's a bit annoying but ok.
The solution for MySQL seems to use the CountWalker for queries without GROUP BY or HAVING clause and CountOutputWalker for other queries. A better solution would be to create a custom hand-made count query for queries with GROUP BY or HAVING clause. This is possible in "knp-components Pager" and, I suppose, in the other pagination libraries using doctrine-orm Tools.

But as "knp-components Pager" does not use the "doctrine-orm Tools Paginator" but only the walkers from doctrine-orm, it doesn't have the boolean flag to switch between CountWalker and CountOutputWalker. The CountOutputWalker is used if doctrine-orm version is 2.3.0 or more and that's it.
I've made a pull request to activate the use of CountWalker for queries without HAVING clause but I think now it's a bad idea. The solution should be to add the same boolean flag to the "knp-components Pager" library. I will talk to them about that.

Thanks again for your time :)

[doctrinebot]

Comment created by stof:

See KnpLabs/knp-components#114

[doctrinebot]

Comment created by fredpeaks:

:) great