Drizzle Team•12mo ago

Extremely high usage due to weird ordering used by drizzle

After facing a ton of read rows in planetscale, I contacted the support team, the issue seemed to be in a weird piece of the query that drizzle was performing Basically, this query:

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: desc(postsTable.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: desc(postsTable.createdAt),
        limit: 16,
      },
    },
  });

translates to the following sql query:

6 Replies

titongoOP•12mo ago

https://www.coderstool.com/cs/29V27y

No Code Developers Tools - CodersTool.com

Share Code Snippets

Code snippets can improve your workflows and save time coding. Use this online tool to share code snippets.

titongoOP•12mo ago

particularly, this portion of the query seems to be problematic

FROM
  (SELECT *, row_number() OVER (
      ORDER BY `debatesTable_posts`.`created_at` DESC)
      FROM `posts` `debatesTable_posts`
      WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
      LIMIT 16) `debatesTable_posts`), json_array()) AS `posts`

FROM
  (SELECT *, row_number() OVER (
      ORDER BY `debatesTable_posts`.`created_at` DESC)
      FROM `posts` `debatesTable_posts`
      WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
      LIMIT 16) `debatesTable_posts`), json_array()) AS `posts`

for some reason, the whole row_number() thing is slowing things down, and what is causing it is orderBy: desc(postsTable.createdAt) I tried replacing it with

FROM
  (SELECT *
   FROM `posts` `debatesTable_posts`
   WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
   LIMIT 16 ORDER BY `debatesTable_posts`.`created_at` DESC) `debatesTable_posts`), json_array()) AS `posts`

FROM
  (SELECT *
   FROM `posts` `debatesTable_posts`
   WHERE `debatesTable_posts`.`debate_id` = `debatesTable`.`id`
   LIMIT 16 ORDER BY `debatesTable_posts`.`created_at` DESC) `debatesTable_posts`), json_array()) AS `posts`

and row reads went from 20.000 to 37.

Angelelz•12mo ago

Wow Please open a GitHub issue. Let's see if we can get a fix

titongoOP•12mo ago

Sure! If anyone is facing something similar, here is my (extremely ugly) workaround:

const problemString = `from (select *, row_number() over (order by \`debatesTable_posts\`.\`created_at\` desc) from \`posts\` \`debatesTable_posts\` where \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` limit 16) \`debatesTable_posts\`), json_array()) as \`posts\``;
const replacedWith = `FROM (SELECT * FROM \`posts\` \`debatesTable_posts\` WHERE \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` ORDER BY \`debatesTable_posts\`.\`created_at\` DESC LIMIT 16) \`debatesTable_posts\`), json_array()) AS \`posts\``;

const connection = connect({
  host: config.dbHost,
  username: config.dbUsername,
  password: config.dbPassword,
  fetch: (url, options) => {
    let modifiedBody = options?.body || "";
    if (!modifiedBody || modifiedBody.indexOf("row_number()") !== -1) {
      modifiedBody = modifiedBody.replace(problemString, replacedWith);
    }
    return fetch(url, { ...options, body: modifiedBody });
  },
});

const problemString = `from (select *, row_number() over (order by \`debatesTable_posts\`.\`created_at\` desc) from \`posts\` \`debatesTable_posts\` where \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` limit 16) \`debatesTable_posts\`), json_array()) as \`posts\``;
const replacedWith = `FROM (SELECT * FROM \`posts\` \`debatesTable_posts\` WHERE \`debatesTable_posts\`.\`debate_id\` = \`debatesTable\`.\`id\` ORDER BY \`debatesTable_posts\`.\`created_at\` DESC LIMIT 16) \`debatesTable_posts\`), json_array()) AS \`posts\``;

const connection = connect({
  host: config.dbHost,
  username: config.dbUsername,
  password: config.dbPassword,
  fetch: (url, options) => {
    let modifiedBody = options?.body || "";
    if (!modifiedBody || modifiedBody.indexOf("row_number()") !== -1) {
      modifiedBody = modifiedBody.replace(problemString, replacedWith);
    }
    return fetch(url, { ...options, body: modifiedBody });
  },
});

titongoOP•12mo ago

here are my planetscale row reads

titongoOP•12mo ago

and here's the latency

Gaming

Programming

Extremely high usage due to weird ordering used by drizzle