I am displaying user's favorite team's 3 upcoming and past 3 matches from fixtures table. My query is working fine but I need to improve the query. Because I face high CPU load when around 100 user is active on app. I am not expert at mongoDB.
I have stored users favorite team ids in users table. Sample data from users table
[{
"_id":1,
"favorite_team_ids" : "#228764#,#742#,#18#,#19#",
"udid" : "724DDF36-",
"state" : "1",
"PN_Token" : "e2304daed",
"status":"active"
},
{
"_id":2,
"favorite_team_ids" : "#17#,#742#,#28231#,#19#",
"udid" : "724DD545A-",
"state" : "1",
"PN_Token" : "e2304daed",
"status":"active"
}]
I get team ids with separate mongodb query and and explode them into array. Then I loop against every team id from fixtures table to get data of 3 upcoming and 3 past matches with $lte timestamp
Sample data from Fixtures Table
[{
"_id" : 10443712,
"league_id" : 1270,
"season_id" : 14977,
"stage_id" : 7743503,
"round_id" : 155104,
"venue_id" : 292239,
"localteam_id" : 228764,
"visitorteam_id" : 28231,
"weather_report" : null,
"commentaries" : false,
"attendance" : null,
"pitch" : null,
"winning_odds_calculated" : false,
"formations" : {
"localteam_formation" : null,
"visitorteam_formation" : null
},
"scores" : {
"localteam_score" : 0,
"visitorteam_score" : 0,
"localteam_pen_score" : 0,
"visitorteam_pen_score" : 0,
"ht_score" : null,
"ft_score" : null,
"et_score" : null
},
"time" : {
"status" : "NS",
"starting_at" : {
"date_time" : "2018-11-01 00:00:00",
"date" : "2018-11-01",
"time" : "00:00:00",
"timestamp" : 1541030400,
"timezone" : "UTC"
},
"minute" : null,
"second" : null,
"added_time" : null,
"extra_minute" : null,
"injury_time" : null
},
"localTeam" : {
"data" : {
"id" : 228764,
"legacy_id" : null,
"name" : "Mazarrón FC",
"short_code" : null,
"twitter" : null,
"country_id" : null,
"national_team" : null,
"founded" : null,
"logo_path" : null,
"venue_id" : null
}
},
"visitorTeam" : {
"data" : {
"id" : 28231,
"legacy_id" : 10184,
"name" : "UCAM Murcia II",
"short_code" : null,
"twitter" : null,
"country_id" : 32,
"national_team" : false,
"founded" : 0,
"logo_path" : "",
"venue_id" : 20474
}
},
"venue" : {
"data" : {
"id" : 292239,
"name" : "Estadio Municipal de Mazarrón (Mazarrón)",
"surface" : "",
"address" : "",
"city" : "Mazarrón",
"capacity" : null,
"image_path" : null,
"coordinates" : null
}
},
"added_on" : "2018-11-01 04:42:27",
"updated_on" : "2018-11-01 04:44:35"
},
{
"_id" : 10652017,
"league_id" : 331,
"season_id" : 13253,
"stage_id" : 7743445,
"round_id" : 156903,
"venue_id" : null,
"referee_id" : null,
"localteam_id" : 742,
"visitorteam_id" : 27912,
"weather_report" : null,
"commentaries" : false,
"attendance" : null,
"pitch" : null,
"winning_odds_calculated" : false,
"formations" : {
"localteam_formation" : null,
"visitorteam_formation" : null
},
"scores" : {
"localteam_score" : 0,
"visitorteam_score" : 0,
"localteam_pen_score" : 0,
"visitorteam_pen_score" : 0,
"ht_score" : null,
"ft_score" : null,
"et_score" : null
},
"time" : {
"status" : "NS",
"starting_at" : {
"date_time" : "2018-11-01 13:00:00",
"date" : "2018-11-01",
"time" : "13:00:00",
"timestamp" : 1541077200,
"timezone" : "UTC"
},
"minute" : 0,
"second" : null,
"added_time" : null,
"extra_minute" : null,
"injury_time" : null
},
"leg" : "1/1",
"colors" : null,
"deleted" : false,
"localTeam" : {
"data" : {
"id" : 742,
"legacy_id" : 861,
"name" : "Apollon Smirnis",
"short_code" : null,
"twitter" : null,
"country_id" : 125,
"national_team" : false,
"founded" : 1891,
"logo_path" : "",
"venue_id" : 223
}
},
"visitorTeam" : {
"data" : {
"id" : 27912,
"legacy_id" : 9174,
"name" : "Apollon Paralimniou",
"short_code" : null,
"twitter" : null,
"country_id" : 125,
"national_team" : false,
"founded" : 0,
"logo_path" : "",
"venue_id" : 22197
}
},
"added_on" : "2018-11-01 04:42:27",
"updated_on" : "2018-11-01 04:44:36"
}]
Basically I am using two queries to get single team's data. 1st to get 3 upcoming matches and 2nd for past 3 matches
Query for upcoming matches
{
"find" : "tbl_fixtures",
"filter" : {
"$or" : [
{
"localteam_id" : 228481
},
{
"visitorteam_id" : 228481
}
],
"time.starting_at.timestamp" : {
"$gte" : 1545220545
}
},
"projection" : {
"league_id" : 1,
"season_id" : 1,
"stage_id" : 1,
"round_id" : 1,
"scores" : 1,
"time" : 1,
"localTeam" : 1,
"visitorTeam" : 1,
"_id" : 1
},
"sort" : {
"time.starting_at.timestamp" : 1
},
"limit" : 3,
}
Query for past matches
{
"find" : "tbl_fixtures",
"filter" : {
"$or" : [
{
"localteam_id" : 228481
},
{
"visitorteam_id" : 228481
}
],
"time.starting_at.timestamp" : {
"$lte" : 1545220545
}
},
"projection" : {
"league_id" : 1,
"season_id" : 1,
"stage_id" : 1,
"round_id" : 1,
"scores" : 1,
"time" : 1,
"localTeam" : 1,
"visitorTeam" : 1,
"_id" : 1
},
"sort" : {
"time.starting_at.timestamp" : 1
},
"limit" : 3,
}
I am using MongoDB 4.0.4 with PHP 7.2 on CentOS 7