In MongoDB, if an index is on 3 fields, we can use that index when querying on 2 fields? (wildcard on the 3rd field)

Question

If there is an index on

page_type, our_id, date

and when querying,

db.analytics.find({page_type: 'ingredients', ga_date: 
  {$gte : new Date('Wed Sep 08 2010 12:00:00 GMT-0800')}})

db.analytics.find({page_type: 'ingredients', ga_date: 
  {$gte : new Date('Wed Sep 08 2010 12:00:00 GMT-0800')}}).explain()

if our_id is omitted, or date is omitted, it can still use the index, with something like the following in the explain() output:

"our_id" : [
    [
        {
            "$minElement" : 1
        },
        {
            "$maxElement" : 1
        }
    ]
],

even if both our_id and date are omitted, the index can still be used.

However, when page_type is omitted, no index can be used (as shown in explain()). So is it true that in MongoDB, when part of the index is something of a sequence, like number or date, then it can be omitted when querying? Is this true in the relational DB as well, because I think it might be strictly on the 3 fields if that index was based on those 3 fields.

Answer 1

Those are B-tree indexes, so they can be used for a prefix subset of the columns involved. If you do not have the leading columns, an index range scan (the operation that a B-tree index is primarily used for) is no longer possible. There may be other ways to still use the index (Oracle has fast full scans and skip scans for example), but usually, the index will not be used.

This reasoning applies to everything that uses B-tree indexes, relational DB or not.

Again, this does not depend on the type of the column, but on the order of columns in the index. You need to have the leading columns (in your case, you need page_type). If you have many queries without the page_type, consider recreating the index with page_type as the last column (which of course may also have a negative impact on other queries). In general, you need to know what kind of queries you will run before you can design the indexes.