How to sort by complex primary key?

Question

MariaDB

Let's say there is a table of bookshelves on which books stand:

CREATE TABLE `bookshelf` (
  `bookshelf_id` VARCHAR(10) NOT NULL,
  `book_name` VARCHAR(512) NOT NULL,
  `book_id` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`bookshelf_id`, `book_name`(128), `book_id`)
);

I need to get pagination, in the order given by PRIMARY KEY.

EXPLAIN SELECT * FROM `bookshelf` ORDER BY `bookshelf_id` LIMIT 5;

says that PRIMARY KEY will be used and 5 records will be viewed, but it is clear that the books will not be sorted.

EXPLAIN SELECT * FROM `bookshelf` ORDER BY `bookshelf_id`, `book_name` LIMIT 5;

says it will scan the entire table.
So how do you make the database sort only on the `book_name` part of the field according to the PRIMARY KEY?

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/order-by-o...

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it may still use indexes to find the rows that match the WHERE clause. Examples:
- There is an index on only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if only the first 10 bytes of a CHAR(20) column are indexed, the index cannot distinguish values ​​past the 10th byte and a filesort is needed.