On the optimization of mongodb query subdocuments (LOL vs. details query)

  mongodb, question

图片描述

The picture shows a document structure in mongodb, which records the details of a LOL match.
There are 10 players in participants, with the first 5 TEAMIDs being 100 and the last 5 TEAMIDs being 200. The result of the competition, which team won, is recorded in the sub-document teams.
I now want to inquire about the victories of championId 64 (blind monk) and 157 (asso) when the two heroes are in the same team (game version number > 6.7), the query statement I write like this:

db.getCollection('matches').count({
 $and: [
 { "matchVersion": {$gte:"6.7"} }
 , {
 $or:
 [
 {
 $and:
 [
 { "participants": {$elemMatch: {"teamId": 100, "championId": 64 } } }
 ,   { "participants": {$elemMatch: {"teamId": 100, "championId": 157 } } }
 ,   { "teams":{ $elemMatch: {"teamId": 100, "winner":true} } }
 ]
 },
 {
 $and:
 [
 { "participants": {$elemMatch: {"teamId": 200, "championId": 64 } } }
 ,   { "participants": {$elemMatch: {"teamId": 200, "championId": 157 } } }
 ,   { "teams":{ $elemMatch: {"teamId": 200, "winner":true} } }
 ]
 }
 ]
 }
 ]
 }
 )

The data size is 140,000, but it takes 3 seconds to execute such a query. The corresponding query has been indexed. The results of the query explain are as follows
图片描述

However, it seems that some indexes are not used, for exampleteams.teamId,teams.winnerThe composite index of the,matchVersionIndex of

How should this query be optimized? I think this data size took so long should be my use posture is wrong?

Your execution plan index is used, but it can be seen that the efficiency is not high, but a lot of key information has been folded and the details cannot be seen. The next time it is better to send the original JSON directly, it will be easier to understand. Similarly, if there are data samples, it is better to send a JSON, so that others can have a test data when solving the problem, which is much more convenient.
$andThis thing doesn’t appear most of the time, and the two parallel elements in an object are the relation with. This can simplify your query structure and make others look easier. Therefore, your query has been simplified as follows:

db.getCollection('matches').count({
 "matchVersion": {$gte: "6.7"},
 $or: [{
 "participants": {$elemMatch: {"teamId": 100, "championId": 64}},
 "participants": {$elemMatch: {"teamId": 100, "championId": 157}},
 "teams": {$elemMatch: {"teamId": 100, "winner": true}}
 }, {
 "participants": {$elemMatch: {"teamId": 200, "championId": 64}},
 "participants": {$elemMatch: {"teamId": 200, "championId": 157}},
 "teams": {$elemMatch: {"teamId": 200,"winner": true}}
 }]
 })

Finally, the most critical index problem is that the index that is more useful to you is supposed to beparticipants.teamId+participants.championId+teams.teamId+teams.winner+matchVersionAccording to the filterability of the condition, the condition with better filterability should be put in front. Even some conditions are removed to improve writing efficiency. But it depends on your data distribution.
Why is your index not used? mongodb 2.6 supports cross-indexing and can use multiple indexes to satisfy the same query, but the current implementation plan evaluation system makes cross-indexing difficult to trigger. So try to use an index to satisfy your query.