Pagination getting slower while page number increasing

As already mentioned, OFFSET must get and toss all the rows before the desired ones.

If you can code the query to "remember where you left off", there is a much faster way than using OFFSET.

Here is a MySQL-specific discussion of such: http://mysql.rjweb.org/doc.php/pagination

The slowdown happens because of the way OFFSET works: it fetches all the data and only then drops the part before the offset. For your case it means the grouping will happen not only for the current page, but for all the previous pages too.

The standard trick to fix this kind of problem is to use Keyset Pagination. When fetching the page, you need to remember its last parent. Then in order to fetch the next page, you use your query with the

WHERE parent > YOUR_LAST_PARENT

clause and no OFFSET. The RDBMS will see that parent is indexed and quickly navigate the index to the correct parent.

EDIT. The first version of the answer recommended a HAVING clause. HAVING clause might work too, but a WHERE clause is enough, I was thrown off by the grouping.