Theo's Typesafe Cult•9mo ago

SQL/Drizzle Where Query Search Optimization

Hi, originally I wanted a fuzzy search but now I just wanted to look how far I can get with just a where query. I have a few concerns with the reads this query causes to the database. Especially I want to know if I can stop if I already have 5 via the name or if you generally have some read and performance optimizations.

ctx.db.query.city.findMany({
        limit: 5,
        where: (users, { like, or }) =>
          or(
            or(
              like(users.name, input.name + "%"),
              like(users.name, "%" + input.name),
            ),
            or(
              like(users.germanName, input.name + "%"),
              like(users.germanName, "%" + input.name),
            ),
            or(
              like(users.region, input.name + "%"),
              like(users.region, "%" + input.name),
            ),
          ),

ctx.db.query.city.findMany({
        limit: 5,
        where: (users, { like, or }) =>
          or(
            or(
              like(users.name, input.name + "%"),
              like(users.name, "%" + input.name),
            ),
            or(
              like(users.germanName, input.name + "%"),
              like(users.germanName, "%" + input.name),
            ),
            or(
              like(users.region, input.name + "%"),
              like(users.region, "%" + input.name),
            ),
          ),

4 Replies

Astra•9mo ago

you can write the query and would technically get the same result

or(
  like(users.name, "%" +  input.name + "%"),
  like(users.germanName, "%" + input.name + "%"),
  like(users.region, "%" +  input.name + "%"),
),

or(
  like(users.name, "%" +  input.name + "%"),
  like(users.germanName, "%" + input.name + "%"),
  like(users.region, "%" +  input.name + "%"),
),

you can also use ilike if you dont want it to be case sensitive

Neto•9mo ago

like/ilike searches are awful to optimize basically impossible without the proper tool

FleetAdmiralJakob 🗕 🗗 🗙OP•9mo ago

Ok, I will try a different way Ilike is not supported by SQLite anyway does it make sense to make a composite index with the name, germanName and region? currently I only have an index of each of them alonely

JulieCezar•9mo ago

One thing you can do - although it leads to some complications and redundancy - create a new field where you concat name, germanName and region and then you can search only that one field However, now you will have to update that field whenever any of the other 3 changes

Gaming

Programming

SQL/Drizzle Where Query Search Optimization