skip to Main Content

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


  1. You have to use eager loading or select specific columns

    Login or Signup to reply.
  2. "Remember where you left off" instead of using OFFSET: Pagination

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search