Help with deciding when to use indexes.

I have the following schema:

// missing imports from drizzle to avoid discord character limit
import { teamsTable } from "./clubSchemas";
import crypto from "node:crypto";

const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const usersTable = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  name: text("name").notNull(),
  teamId: text("team_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  lastEdited: timestamp("last_edited").defaultNow().notNull(),
});

export const usersRelations = relations(usersTable, ({ many, one }) => ({
  posts: many(postsTable),
  team: one(teamsTable, {
    fields: [usersTable.teamId],
    references: [teamsTable.id],
  }),
}));

export const postsTable = mysqlTable("posts", {
  id: v4ID("id").primaryKey(),
  authorId: text("author_id").notNull(),
  content: text("content").notNull(),
  debateId: text("debate_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  likeCount: mediumint("like_count").default(0).notNull(),
  dislikeCount: mediumint("dislike_count").default(0).notNull(),
});

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],
  }),
}));

export const debatesTable = mysqlTable("debates", {
  id: v4ID("id").notNull(),
  name: varchar("name", { length: 255 }).primaryKey(),
  teamId: text("team_id"),
  debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
});

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));

// missing imports from drizzle to avoid discord character limit
import { teamsTable } from "./clubSchemas";
import crypto from "node:crypto";

const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const usersTable = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  name: text("name").notNull(),
  teamId: text("team_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  lastEdited: timestamp("last_edited").defaultNow().notNull(),
});

export const usersRelations = relations(usersTable, ({ many, one }) => ({
  posts: many(postsTable),
  team: one(teamsTable, {
    fields: [usersTable.teamId],
    references: [teamsTable.id],
  }),
}));

export const postsTable = mysqlTable("posts", {
  id: v4ID("id").primaryKey(),
  authorId: text("author_id").notNull(),
  content: text("content").notNull(),
  debateId: text("debate_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  likeCount: mediumint("like_count").default(0).notNull(),
  dislikeCount: mediumint("dislike_count").default(0).notNull(),
});

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],
  }),
}));

export const debatesTable = mysqlTable("debates", {
  id: v4ID("id").notNull(),
  name: varchar("name", { length: 255 }).primaryKey(),
  teamId: text("team_id"),
  debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
});

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));

16 Replies

titongoOP•13mo ago

I am thinking about adding indexes to some fields, but I am not sure. For instance, one of my queries looks like this:

const queryDebatePosts = async (debateName: string) => {
  const select = await 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,
      },
    },
  });
  return select;
};

const queryDebatePosts = async (debateName: string) => {
  const select = await 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,
      },
    },
  });
  return select;
};

Would it help if I add an index to the debate name? The debates table has something like 200 rows and won't grow past that. What does need an index is the post relation I think.

 posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },

 posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },

should I index the createdAt field for faster sorting? or should I index the posts debate_id? what about the post's author_id column? I have started learning about indexes today and I heard the following rules of thumb: "Create an index for anything that goes into a WHERE clause" "foreign keys/pointers to other tables should be indexed" since my relations are mostly dependent on ids that point to other tables (posts.author_id, posts.debate_id, debates.team_id) my guess is that indexing all of them should be an improvement.

Angelelz•13mo ago

for tables with 200 rows there won't be any notable difference If this is for learning, yes add all the indices you think you need But in real applications you should add them when you know you need them

titongoOP•13mo ago

This is a real app unfortunately, it blew up on twitter and it got to 5 billion row reads, which forced me to learn about indexes lol Is there any problem with "over-indexing"?

Angelelz•13mo ago

Uff, yes, add the indexes The only ptoblem would be storage

titongoOP•13mo ago

There's bigger fish to fry

Angelelz•13mo ago

Yes, just add them in all the where, foreign keys and orderbys

titongoOP•13mo ago

Thanks a lot! should I add them both in the source table and the field which references it?

Angelelz•13mo ago

Is this planetscale? You have explain analyse in mysql

titongoOP•13mo ago

for instance:

export const postsTable = mysqlTable(
  "posts",
  {
    id: v4ID("id").primaryKey(),
    authorId: text("author_id").notNull(),
    content: text("content").notNull(),
    debateId: text("debate_id").notNull(),
  },
  (table) => {
    return {
      debateIdIdx: index("debate_id_idx").on(table.debateId),
      authorIdIdx: index("author_id_idx").on(table.authorId),
    };
  }
);
export const postLikesTable = mysqlTable(
  "post_likes",
  {
    postId: varchar("post_id", { length: 36 }).notNull(),
    userId: varchar("user_id", { length: 36 }).notNull(),
    isLike: boolean("is_like"),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.postId, table.userId] }),
       //is this necessary?
      postIdIdx: index("post_id_idx").on(table.postId),
    };
  }
);

export const postsTable = mysqlTable(
  "posts",
  {
    id: v4ID("id").primaryKey(),
    authorId: text("author_id").notNull(),
    content: text("content").notNull(),
    debateId: text("debate_id").notNull(),
  },
  (table) => {
    return {
      debateIdIdx: index("debate_id_idx").on(table.debateId),
      authorIdIdx: index("author_id_idx").on(table.authorId),
    };
  }
);
export const postLikesTable = mysqlTable(
  "post_likes",
  {
    postId: varchar("post_id", { length: 36 }).notNull(),
    userId: varchar("user_id", { length: 36 }).notNull(),
    isLike: boolean("is_like"),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.postId, table.userId] }),
       //is this necessary?
      postIdIdx: index("post_id_idx").on(table.postId),
    };
  }
);

Yes

Angelelz•13mo ago

Make sure your queries are not doing a full table scan

titongoOP•13mo ago

Oh they are that's what planetscale support told me and they advised me to look into indexes

titongoOP•13mo ago

these are absolutely nightmarish stats

Angelelz•13mo ago

Use drizzle logger, copy the query, go to the console, paste it and put explain analyze in front Start adding indexes and the do that again and compare Good luck

titongoOP•13mo ago

will try, thanks! could you please explain this? I am kind of confused for instance, if I'm pointing to a primary key, should the field that points to it be indexed?

Angelelz•13mo ago

I don't think that index is necessary It's already covered by the primary key Unless you are ordering by it descending

titongoOP•13mo ago

Ok thanks!

Gaming

Programming

Help with deciding when to use indexes.