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

Answer 1

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.