-
Type: Improvement
-
Resolution: Won't Fix
-
Priority: Minor - P4
-
None
-
Affects Version/s: None
-
Component/s: Index Maintenance
-
Labels:None
-
Storage Execution
SQL Server has the ability to include fields within an index definition that just tag along for the ride but are not used for indexing (sorting) purposes at all. This has a great benefit of turning an index into a covering index without extra sorting overhead if any of the values change. (see http://msdn.microsoft.com/en-us/library/ms190806.aspx)
In our case, we have an array of subdocuments that we need to validate using $elemMatch. Unfortunately, $elemMatch can't take advantage of indexes (see SERVER-964). Even though this part of our query evaluates to true 99% of the time, we cannot denormalize it further. On really big queries, with large ntoskip values, Mongo has to do a table lookup for every record in the query, resulting in queries returning only 50 records taking over 1 minute.
But if Mongo could "include" a snippet of the document on the leaf of every indexed item, the query would be able to be satisfied completely by the index and the same query would take seconds or milliseconds to complete.