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?
2
Answers
You have to use eager loading or select specific columns
"Remember where you left off" instead of using
OFFSET
: Pagination