Spring PagingAndSortingRepository very slow on large database?

Question

I have a large database (30 mio rows).

And now discovered that using PagingAndSortingRepository with Pageable to limit the query has a very bad performance. But why?

interface MyRepo extends PagingAndSortingRepository<MyEntity, Long> {

    @Query(value = "SELECT * FROM mytable ORDER BY id limit 10",
            nativeQuery = true)
    List<Object> findAllLimited();

    @Query(value = "SELECT * FROM mytable",
            countQuery = "SELECT count(*) FROM mytable ORDER BY id",
            nativeQuery = true)
    Page<Object> findAllPageable(Pageable pageable);
}

Usage:

dao.findAllLimited(); //10ms
dao.findAllPageable(PageRequest.of(0, 10)); //20000ms

Why is a Page request through PagingAndSortingRepository that slow? It does not matter here if I use native or normal query. As soon as I try to apply pagination/limit by a Pageable, the performance collapses.

Any idea why this could be the case? The native LIMIT 10 approach proves that the db in general is able to serve the results just in time.

The table is defined like:

CREATE TABLE mytable (
 id bigint NOT NULL AUTO_INCREMENT,
  ...
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

first count is always slow second a LIMIT without ORDER BY is not good Last good indexes make queries faster — nbk, Jul 26 '22 at 13:56
`countQuery = "SELECT count(*) FROM mytable"` - that is the reason — Andrey B. Panfilov, Jul 26 '22 at 13:57
@AndreyB.Panfilov any advise how to fix that? I'm looking for real pagination (the LIMIT 10 query is just a test to prove that the db is fine in general, and indexes also). — membersound, Jul 26 '22 at 13:59
@nbk I added `ORDER BY id` to both statements, but problem remains! The index should not be the problem, because if the `LIMIT 10` query works, why should the `Pageable` not? — membersound, Jul 26 '22 at 14:01
Post also the indexes involving id, and there is no cure for count, you really need it regularly you make a new table with one row and update the count with triggers. — nbk, Jul 26 '22 at 14:04
I don't know if I really need the count. All I'm trying is to make the `Pageable` approach work. See update for table sql. — membersound, Jul 26 '22 at 14:07
@membersound https://stackoverflow.com/questions/12644749/way-to-disable-count-query-from-pagerequest-for-getting-total-pages — Andrey B. Panfilov, Jul 26 '22 at 14:07

score 1 · Accepted Answer · answered Jul 26 '22 at 15:06

Slice<Object> findBy(Pageable pageable) solved the problem! So the comments above are correct: the bottleneck is the count query that is executed for each page additionally by Spring.

If anybody has a solution for this that could retain the Page with it's total elements count, but without having to execute the count on each page request, please comment.

Apart from that, the slice solution works, except that one does not know how many pages are yet to come.

Spring PagingAndSortingRepository very slow on large database?

1 Answers1