Can we optimize this query?
export const getTrendingFilterTags = query({
args: { platformType: v.string() },
handler: async ({ db }, args) => {
const dictionaryCategories = await getManyFrom(
db,
"dictionaryCategories",
"by_platform_type",
args.platformType,
"platformType",
);
const categoryIds = new Set(
dictionaryCategories.map((category) => category._id),
);
const dictionarySubCategories = await filter(
db.query("dictionarySubCategories"),
(subCategory) => categoryIds.has(subCategory.dictionaryCategoriesId),
).collect();
const subCategoryIds = new Set(
dictionarySubCategories.map((subCategory) => subCategory._id),
);
const dictionaryEntries = await filter(
db.query("dictionaryEntries"),
(entry) => subCategoryIds.has(entry.dictionarySubCategoriesId),
).collect();
const entryIds = new Set(dictionaryEntries.map((entry) => entry._id));
const bareTrendingTags = await filter(
db.query("trendingFilterTags"),
(tag) => entryIds.has(tag.dictionaryEntry),
).collect();
const trendingTags = await asyncMap(bareTrendingTags, async (tag) => {
const dictionaryEntry = dictionaryEntries.find(
(entry) => entry._id === tag.dictionaryEntry,
);
const dictionarySubCategory = dictionarySubCategories.find(
(subCategory) =>
subCategory._id === dictionaryEntry?.dictionarySubCategoriesId,
);
const dictionaryCategory = dictionaryCategories.find(
(category) =>
category._id === dictionarySubCategory?.dictionaryCategoriesId,
);
return //Something
return trendingTags;
},
});
export const getTrendingFilterTags = query({
args: { platformType: v.string() },
handler: async ({ db }, args) => {
const dictionaryCategories = await getManyFrom(
db,
"dictionaryCategories",
"by_platform_type",
args.platformType,
"platformType",
);
const categoryIds = new Set(
dictionaryCategories.map((category) => category._id),
);
const dictionarySubCategories = await filter(
db.query("dictionarySubCategories"),
(subCategory) => categoryIds.has(subCategory.dictionaryCategoriesId),
).collect();
const subCategoryIds = new Set(
dictionarySubCategories.map((subCategory) => subCategory._id),
);
const dictionaryEntries = await filter(
db.query("dictionaryEntries"),
(entry) => subCategoryIds.has(entry.dictionarySubCategoriesId),
).collect();
const entryIds = new Set(dictionaryEntries.map((entry) => entry._id));
const bareTrendingTags = await filter(
db.query("trendingFilterTags"),
(tag) => entryIds.has(tag.dictionaryEntry),
).collect();
const trendingTags = await asyncMap(bareTrendingTags, async (tag) => {
const dictionaryEntry = dictionaryEntries.find(
(entry) => entry._id === tag.dictionaryEntry,
);
const dictionarySubCategory = dictionarySubCategories.find(
(subCategory) =>
subCategory._id === dictionaryEntry?.dictionarySubCategoriesId,
);
const dictionaryCategory = dictionaryCategories.find(
(category) =>
category._id === dictionarySubCategory?.dictionaryCategoriesId,
);
return //Something
return trendingTags;
},
});
2 Replies
One example, the second and third statement:
can be optimized by using an index on the
const categoryIds = new Set(
dictionaryCategories.map((category) => category._id),
);
const dictionarySubCategories = await filter(
db.query("dictionarySubCategories"),
(subCategory) => categoryIds.has(subCategory.dictionaryCategoriesId),
).collect();
const categoryIds = new Set(
dictionaryCategories.map((category) => category._id),
);
const dictionarySubCategories = await filter(
db.query("dictionarySubCategories"),
(subCategory) => categoryIds.has(subCategory.dictionaryCategoriesId),
).collect();
dictionarySubCategories
table, so that you fetch only those documents that have one of the categoryIds
(assuming categoryIds
is a relatively small list).This might be a helpful post:
https://stack.convex.dev/databases-are-spreadsheets
Databases are Spreadsheets
I want to share my mental model of databases:
- Databases are just big spreadsheets
- An index is just a view of the spreadsheet sorted by one or mor...