Is This SQL Query Efficient? Seeking Advice!
I'm quite beginner to SQL and Drizzle, and I wrote a query, but I have no idea how efficient it is. I don’t even know what to look for. I asked AI, and according to it, my query is inefficient because it contains a subquery that runs for every row in the database:
where: or(
eq(topping_categories.isGlobal, true),
inArray(
topping_categories.id,
adminDB
.select({ id: toppings_dishes_connect.toppingCategoryID })
.from(toppings_dishes_connect)
.where(eq(toppings_dishes_connect.dishID, dishId))
)
),
But if these functions exist, why shouldn’t they be used?
The main goal is that restaurants can add dishes, and each dish can belong to multiple dish groups, which I manage through a junction table. I want to fetch the associated dish groups as well as global ones that don’t need to be assigned to a dish because they apply to all dishes.
The image shows the full query and table structure.
What do you think about my approach? Thanks in advance for any help—I’d really appreciate any guidance!
where: or(
eq(topping_categories.isGlobal, true),
inArray(
topping_categories.id,
adminDB
.select({ id: toppings_dishes_connect.toppingCategoryID })
.from(toppings_dishes_connect)
.where(eq(toppings_dishes_connect.dishID, dishId))
)
),
But if these functions exist, why shouldn’t they be used?
The main goal is that restaurants can add dishes, and each dish can belong to multiple dish groups, which I manage through a junction table. I want to fetch the associated dish groups as well as global ones that don’t need to be assigned to a dish because they apply to all dishes.
The image shows the full query and table structure.
What do you think about my approach? Thanks in advance for any help—I’d really appreciate any guidance!
