Why does the doctrine build such complex queries to get the number of elements?

Question

symfony

Hello everyone,
I have a Symfony2 project. ORM - Doctrine2. The Loggable
extension is used to keep a log of object changes. There are now about 700,000 entries in the log table. In the admin panel, these entries are displayed page by page using the standard doctrine paginator. I noticed that the doctrine for getting the number of records builds the following query:

SELECT COUNT(*) AS dctrn_count
FROM (
      SELECT DISTINCT id0
      FROM (
            SELECT k0_.id AS id0,
                    k0_.action AS action1,
                    k0_.logged_at AS logged_at2,
                    k0_.object_id AS object_id3,
                    k0_.object_class AS object_class4,
                    k0_.version AS version5,
                    k0_.data AS data6,
                    k0_.username AS username7
                    FROM ext_log_entries k0_
                    WHERE
                        k0_.object_class NOT IN ('Class1', 'Class2')
                    ORDER BY k0_.logged_at DESC
            )
      dctrn_result
) dctrn_table

indexes in place. Manually tried to remove WHERE and ORDER - does not help. The request is executed for about 20 seconds, which, to put it mildly, is dofiga.
Tell me, in which direction to dig? Doctrine builds queries incorrectly, or is it a server bug (dedicated Xeon E3-1240 16GB RAM with 4 projects) that the query takes so long to process?

Answer 1

We need to get rid of DISTINCT and the subquery.

Answer 2

popbuy class https://github.com/whiteoctober/Pagerfanta/blob/ma...