Querying Best Practices?

Just want to discuss query best practices.

const userAccount = await db.query.accounts.findFirst({
    where: eq(accounts.id, userId),
    with: {
      activeProfile: {
        with: {
          savedShows: {
            where: eq(myShows.id, input.id),
            limit: 1, // Does using limit matter here? It should speed up query right?
          },
        },
      },
    },
  })

const userAccount = await db.query.accounts.findFirst({
    where: eq(accounts.id, userId),
    with: {
      activeProfile: {
        with: {
          savedShows: {
            where: eq(myShows.id, input.id),
            limit: 1, // Does using limit matter here? It should speed up query right?
          },
        },
      },
    },
  })

And since I only need the nested relations

const userAccount = await db.query.accounts.findFirst({
    columns: {}, // Would this matter? Does it speed up queries? It would transfer less data but is that all?
    where: eq(accounts.id, userId),
    with: {
      activeProfile: {
        with: {
          savedShows: {
            where: eq(myShows.id, input.id),
            limit: 1, 
          },
        },
      },
    },
  })

const userAccount = await db.query.accounts.findFirst({
    columns: {}, // Would this matter? Does it speed up queries? It would transfer less data but is that all?
    where: eq(accounts.id, userId),
    with: {
      activeProfile: {
        with: {
          savedShows: {
            where: eq(myShows.id, input.id),
            limit: 1, 
          },
        },
      },
    },
  })

3 Replies

Andrii Sherman•16mo ago

not sure about limit 1, it may not speed up in cetrain cases, but even make ot worse for not selecting all columns - it will speed up a transfer time from database to a server

Luc LedoOP•16mo ago

how can limit can it worst?

Andrii Sherman•16mo ago

https://stackoverflow.com/questions/21385555/postgresql-query-very-slow-with-limit-1 https://www.postgresql.org/message-id/a8615d56-7e9d-8362-e61e-114fa92f1c02%40enterprisedb.com a few links about this issue but seems like when you are using limit 1 on a high cost queries, query planner thinks, that finding just 1 row will be fast enough to do it directly without using any indexes/etc.

Gaming

Programming

Querying Best Practices?