Compound Indexes on MongoDB

Question

Sorry for my english, I need help on mongodb indexes. I have a capped collection (size: 10GB) with some fields for my application logs. Example structure: Logs[_id, userId, sum, type, time, response, request]. I have created compound index: [userId,time,type]. I get two arrays are grouped records by userId for today, where 'type' is "null" and "1". And my two query example:

 $group = array(
        array(
            '$match' => array(
                'userId' => $userId,
                'time' => array(
                        '$gt' => date("Y-m-d")
                    ),
                'type' => array('$ne' => null)
                )
            ),
        array(
            '$group' => array(
                "_id" => '$userId',
                "total" => array('$sum' => '$sum'),
                "count" => array('$sum' => 1)
            ),
        )
    );
    $results = $collections->aggregate($group);

    $group = array(
        array(
            '$match' => array(
                'userId' => $userId,
                'time' => array(
                    '$gt' => date("Y-m-d")
                    ),
                'type' => 1
                )
            ),
        array(
            '$group' => array(
                "_id" => '$userId',
                "count" => array('$sum' => 1)
            ),
        )
    );

    $results2 = $collections->aggregate($group);

If current user has more 100000 documents on collection for today - the speed of my query is very slow (more 10 sec). Give me some advices on creating the right index, please :) Thanks.

Answer 1

Based on the explain that you posted, the correct index is being used (BtreeCursor), it is using only the index (i.e. it is a covered index query - indexOnly is true) and nothing is being matched (n = 0) in this case. So, that all checks out generally, though $ne as a clause in the first example is not going to be very efficient.

However the main issue based on the explain is likely the fact that the index does not appear to be fully in memory. There are 13 yields listed and the most common reason for a query like this to yield is when it has to fault to disk to page something in. Since, as mentioned previously, it is only using the index, those yields imply faults to disk for the index and hence indicate that the whole index is not in memory.

If you re-run the query immediately after this it should be faster (assuming the index can actually fit into available memory) because the index will have been paged in by the first run. If it is still slow on the second run and showing yields, then you either don't have enough memory to hold the index in memory or something else is evicting it from memory and you essentially have memory contention causing performance problems.