[SERVER-7316] Request to add "Included Columns" as an option for index types Created: 10/Oct/12  Updated: 06/Dec/22  Resolved: 04/Mar/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: William Zola Assignee: Backlog - Storage Execution Team
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Assigned Teams:
Storage Execution
Participants:

 Description   

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.


Generated at Thu Feb 08 03:14:11 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.