[SOLVED] MySQL pagination query is very slow

MySQL pagination query is very slow

I have this table using InnoDB as engine:

create table if not exists Playlists(
    UserId bigint unsigned not null,
    Title varchar(50) not null,
    IsPublic bool not null,
    primary key (UserId, Title),
    foreign key (UserId) references Users(Id)
    on delete cascade
    on update cascade
);

This table has a huge amount of datasets around >1_000_000 and they're increasing. I want to select all playlists that have IsPublic = TRUE and paginate that.

But if I use this

select Title from Playlists where IsPublic = true order by Title limit @myLimit offset @myOffset

the query takes longer and longer.

I also tried using an index like so but no significant improvements either

create index index_playlists_ispublic on Playlists(IsPublic);

Is there any way I can optimize this?

Solution

"Remember where you left off" instead of using OFFSET: Pagination