I have been working on a code which hits the database to get results depending on the targetProductIds. The query getting used is of the format :
SELECT * FROM rules WHERE type= ‘rawRecommendations’ AND algorithm.algorithmName= ‘algo1’ AND algorithm.timeFrame= ‘30’ AND orgId= ‘org1’ AND channelTenantId= ‘ct1’ AND catalogId= ‘catalog1’ AND SOME s IN targetProductId SATISFIES s IN [ “product1”,“product5”,“product6” ] END ORDER BY dateCreated ASC
The query returns documents of the format :
{
“rules”: {
“algorithm”: {
“algorithmName”: “algo1”,
“category”: “NA”,
“measureOfPopularity”: “NA”,
“timeFrame”: “30”,
“weight”: 100
},
“catalogId”: “catalog1”,
“channelTenantId”: “ct1”,
“modelId”: “modelId”,
“orgId”: “org1”,
“recommendations”: [
“product0”
],
“targetProductId”: [
“product12”,
“product6”
],
“type”: “rawRecommendations”,
“uniqueId”: “uid”
}
}
The queries are taking a lot of time to return any response and are often timing out. Can someone help with the index for this queries as the indexes we have tried do not seems to solve the issue.