Performance with 2,000,000 rows

[jungleman12]

Hello Kevin,

I tried this package for pagination, but I'm encountering few problems.

The problem I'm having here is a performance issue: I have 2,000,000 rows in my table, and the problem I'm encountering is that there's a COUNT() query that's taking a long time to execute. Is there a way around this?

Here's the code used:

        $qb = $this->createQueryBuilder('v');

        $qb
            ->orderBy('v.visitedAt', 'DESC')
        ;

        return (new EntityResult($qb))->paginate($page, self::PAGINATOR_PER_PAGE);

I would like to point out that in my template I use this if it can help:
paginator.nextPage
paginator|length

Thanks.

[seb-jean]

I have exactly the same problem.

The problem is that nextPage calls lastPage(). lastPage() calls totalCount(). totalCount() calls $this->collection->count(), and that's when the COUNT is calculated: https://github.com/zenstruck/collection/blob/1.x/src/Collection/Page.php#L92
There might be another way to do it, but I don't know. I'll look this weekend to see if I can find any solutions.

[kbond]

Yeah, for this type of pagination the count is always required. Even if it wasn't the offset/limit is also expensive. Cursor pagination as suggested in #52 is a solution but it complex to implement.