Hi @Leandro_Domingues ,
Thanks for your reply. Please find below the document, query, and indexes. Please note that i have multiple arrays in one document and the query will include most of them. Whenever there is data for a particular query the response is very fast but in case of no record, it tends to slow down. Please suggest.
Document:
{
"_id": {
"$oid": "62c865378392121884010c67ce65418c"
},
"q_id": 1725833882,
"pq_id":0,
"data": [
{
"l_id": 1,
"updated_date": "2022-09-26T18:14:02.729+05"
},
{
"l_id": 2,
"updated_date": "2022-09-26T18:14:02.729+05"
}
],
"sh_id": 6782,
"level": 54,
"status": 1,
"tmp_id": {
"$numberLong": "10"
},
"is_p": false,
"t_data": [
{
"t_id": {
"$numberLong": "303"
},
"t_name": "xyz",
"t_v_id": {
"$numberLong": "6544"
},
"t_v": "tre"
},
{
"t_id": {
"$numberLong": "487"
},
"t_name": "poi",
"t_v_id": {
"$numberLong": "65487"
},
"t_v": "ytre"
}
],
"bl_data": [
{
"s_m_id": 21107024369,
"status": 1,
"s_details": [
{
"s_id": 428097263500,
"s_type": 809
},
{
"s_id":4280876652,
"s_type": 1954
},
{
"s_id": 654378,
"s_type": 857
}
],
"dl_id":12,
"numbers": 4,
"tts": 6000,
"level": 4
}
],
"v_list": [
{
"v": "1",
"used": false,
"level":1
"status": 1
},
{
"v": "2",
"used": true,
"level":2
"status": 1
],
"qt_id":198275
}
Index:
1: qt_id,pq_id,bl_data.s_details.s_id,bl_data.numbers,dl_id,sh_id,bl_data.level
2: sh_id,v_list.level,level.status,pq_id
Query:
{
"$and": [
{
"sh_id": {
"$in": [
17250
]
},
"pq_id": 0
},
{
"v_list": {
"$elemMatch": {
"status": 1,
"level": {
"$in": [
7,
4,
2,
90,
43
]
},
"used": true
}
}
},
{
"data.l_id": {
"$all": [
1,
2
]
}
},
{
"bl_data.s_details": {
"$elemMatch": {
"s_id": {
"$in": [
1870
]
}
}
}
},
{
"$and": [
{
"$or": [
{
"t_data.t_v_id": {
"$in": [
4
]
}
},
{
"t_data.t_v_id": {
"$in": [
1
]
}
}
]
},
{
"$or": [
{
"t_data.t_v_id": {
"$in": [
2
]
}
},
{
"t_data.t_v_id": {
"$in": [
3
]
}
}
]
}
]
}
]
}