Drizzle Team•12mo ago

+21.000 row reads in query, how can I improve performance?

My planetscale free tier has been absolutely demolished, and the most expensive query has a rows read / returned ratio of 14,196. according to their docs, this ratio is: The result of dividing total rows read by rows returned in a query. A high number can indicate that your database is reading unnecessary rows, and they query may be improved by adding an index. the query that is causing this is the following:

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },
    },
  });

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },
    },
  });

61 Replies

titongoOP•12mo ago

The most important parts of my schema look like this:

export const debatesTable = mysqlTable(
  "debates",
  {
    id: v4IDWithDefault("id").primaryKey(),
    name: varchar("name", { length: 255 }).unique().notNull(),
    leagueId: v4Id("league_id"),
    teamId: v4Id("team_id"),
    debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
  },
  (table) => {
    return {
      teamIdIdx: index("team_id_idx").on(table.teamId),
      leagueIdIdx: index("league_id_idx").on(table.leagueId),
    };
  }
);

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(postsTable),
  league: one(leaguesTable, {
    fields: [debatesTable.leagueId],
    references: [leaguesTable.id],
  }),
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));
export const postsTable = mysqlTable(
  "posts",
  {
    id: v4IDWithDefault("id").primaryKey(),
    authorId: v4Id("author_id").notNull(),
    content: text("content").notNull(),
    debateId: v4Id("debate_id").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    likeCount: mediumint("like_count").default(0).notNull(),
    dislikeCount: mediumint("dislike_count").default(0).notNull(),
  },
  (table) => {
    return {
      debateIdIdx: index("debate_id_idx").on(table.debateId),
      authorIdIdx: index("author_id_idx").on(table.authorId),
      createdAtIdx: index("created_at_idx").on(table.createdAt),
    };
  }
);

export const postsRelations = relations(postsTable, ({ one, many }) => ({
  author: one(usersTable, {
    fields: [postsTable.authorId],
    references: [usersTable.id],
  }),
  debate: one(debatesTable, {
    fields: [postsTable.debateId],
    references: [debatesTable.id],
  }),
  likes: many(postLikesTable),
}));

export const debatesTable = mysqlTable(
  "debates",
  {
    id: v4IDWithDefault("id").primaryKey(),
    name: varchar("name", { length: 255 }).unique().notNull(),
    leagueId: v4Id("league_id"),
    teamId: v4Id("team_id"),
    debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
  },
  (table) => {
    return {
      teamIdIdx: index("team_id_idx").on(table.teamId),
      leagueIdIdx: index("league_id_idx").on(table.leagueId),
    };
  }
);

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(postsTable),
  league: one(leaguesTable, {
    fields: [debatesTable.leagueId],
    references: [leaguesTable.id],
  }),
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));
export const postsTable = mysqlTable(
  "posts",
  {
    id: v4IDWithDefault("id").primaryKey(),
    authorId: v4Id("author_id").notNull(),
    content: text("content").notNull(),
    debateId: v4Id("debate_id").notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    likeCount: mediumint("like_count").default(0).notNull(),
    dislikeCount: mediumint("dislike_count").default(0).notNull(),
  },
  (table) => {
    return {
      debateIdIdx: index("debate_id_idx").on(table.debateId),
      authorIdIdx: index("author_id_idx").on(table.authorId),
      createdAtIdx: index("created_at_idx").on(table.createdAt),
    };
  }
);

export const postsRelations = relations(postsTable, ({ one, many }) => ({
  author: one(usersTable, {
    fields: [postsTable.authorId],
    references: [usersTable.id],
  }),
  debate: one(debatesTable, {
    fields: [postsTable.debateId],
    references: [debatesTable.id],
  }),
  likes: many(postLikesTable),
}));

I was pretty sure that adding indexes on foreign keys and created_at would be enough but apparently it is not.

Angelelz•12mo ago

First thing, you obviously have mode: planetscale right? Uff You are ordering by createdAt in the post table and limiting 16 But the order is desc I guarantee that's your problem right there Your index in createdAt is doing absolutly nothing You have to create it descending

titongoOP•12mo ago

I am using drizzle-orm/planetscale-serverless, which I think doesn't require specifying the mode

Angelelz•12mo ago

Unfortunatly you'll have to create it manually in the console because drizzle doesn't support it in the kit yet Which is not bad

titongoOP•12mo ago

ohhh alright I did not know indexes could be descending thanks!

Angelelz•12mo ago

Do this: go to the console and run show index from posts

titongoOP•12mo ago

titongoOP•12mo ago

Do I have to drop then add the index or can I do something like ALTER

Angelelz•12mo ago

Lol, yeah, your indexes are ascending You don't have to drop them run alter table posts add index (created_at desc); Then run show index from posts again and let me see

titongoOP•12mo ago

collation went from A to D so I guess that's it

Angelelz•12mo ago

Hmmm, if you don't ever run asc then that's it Do you have an index in dabates.name?

titongoOP•12mo ago

I think it was added automatically when I specified .unique() in drizzle because it's not present here

Angelelz•12mo ago

Can you run that query again and see the difference?

titongoOP•12mo ago

let me see if I can I am getting these metrics from users

titongoOP•12mo ago

Angelelz•12mo ago

Wow!

titongoOP•12mo ago

Ikr

Angelelz•12mo ago

Check your indexes on degates debates

titongoOP•12mo ago

there are ~150 debates so I never thought about adding a key to the name but it was added automatically so I won't complain

Angelelz•12mo ago

There's a limit on indexes on text, I don't remember, but it shouldn't be relevant in this case Let's do some investigating

titongoOP•12mo ago

I mean finding the debate shouldn't be hard work for the db cause there are only 153 of them but there are a ton of posts

Angelelz•12mo ago

run this on the console: explain select * from posts order by created_at desc limit 16

titongoOP•12mo ago

35.985 to be precise

Angelelz•12mo ago

Show me what you get

titongoOP•12mo ago

should I try adding a WHERE debate_id?

Angelelz•12mo ago

Nah What columns do you need from posts?

titongoOP•12mo ago

just content and created_at, then the ids to merge the author's data

Angelelz•12mo ago

I think that index just solved your biggest problem

titongoOP•12mo ago

oh man

Angelelz•12mo ago

If you want, drop it, run the explain again and compare

titongoOP•12mo ago

you won't beleive this

titongoOP•12mo ago

lmao

titongoOP•12mo ago

that is the time in ms from all queries after modifying the index to be descending it dipped there's something weird though

Angelelz•12mo ago

Lol, you were reading ALL of the columns I mean rows

titongoOP•12mo ago

this is the time in ms specifically in the query I am having problems with

titongoOP•12mo ago

I thought it was fixed but apparently something else produced the dip?

Angelelz•12mo ago

I think you just need to wait Can you show just the last 10 min?

titongoOP•12mo ago

Angelelz•12mo ago

Hmmm

titongoOP•12mo ago

that's just the latency but row reads have the same shape

Angelelz•12mo ago

run this explain analyze select * from posts order by created_at desc limit 16

titongoOP•12mo ago

-> Limit: 16 row(s) (cost=0.0617 rows=16) (actual time=0.0289..0.0876 rows=16 loops=1) -> Index scan on posts using created_at_idx (cost=0.0617 rows=16) (actual time=0.0281..0.0856 rows=16 loops=1)

I think that looks good?

Angelelz•12mo ago

Can't get any better

titongoOP•12mo ago

there's an option in planetscale to run an explain with some parameters I'm gonna run the full query with all of them and see how it goes

Angelelz•12mo ago

Do explain first No analyze

titongoOP•12mo ago

on the full query?

Angelelz•12mo ago

Sure

titongoOP•12mo ago

this explain output is bigger than my db lol

Angelelz•12mo ago

Ohhhh, you're also getting the league and the team Also the team of the author But that explain is pretty good tbh

titongoOP•12mo ago

Ikr??? There's zero reason for 30.000 rows to be read

Angelelz•12mo ago

I guess more investigation is needed Is it still like that?

titongoOP•12mo ago

yup it is rising steadily I guess more rows need to be read as more posts are written its in 33.000 now 💀

Angelelz•12mo ago

There's no way 16 rows and from posts causes that many reads Because the rest are one relations I would need to investigate it more

titongoOP•12mo ago

Man I'm sorry for taking so much time from you

Angelelz•12mo ago

Maybe adding a covering index somewhere idk

titongoOP•12mo ago

Oh guess I have something new to learn for tomorrow thanks for all the help

Angelelz•12mo ago

I think this goes a little beyond drizzle help. dm me if you'd like some private consultation

alexblokh•10mo ago

hey, @titongo did the index worked? can you show the rest of the schema? I'll be able to check

Gaming

Programming

+21.000 row reads in query, how can I improve performance?