[SERVER-37751] Creating indexes for HINT only Created: 25/Oct/18  Updated: 25/Oct/18  Resolved: 25/Oct/18

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

Type: Question Priority: Critical - P2
Reporter: Michael Mikhjian Assignee: Danny Hatcher (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-15463 Exclude indexes from query plan (hint... Backlog
Participants:

 Description   

I'm finding issues in one of our databases that houses over 100GB of data where queries are using wrong indexes... where they could use a better index designed specifically for it's query.  

For example: I have a table of leads with field names i.e., Name, Email, Source (And about 50 other fields with lead details).  There are several reports (let's call it a dozen) that utilize a 10+ or so of the fields.  Originally we designed multiple indexes to serve each report individually, but several fields were needed across the indexes... wasting a good chunk of ram. Eventually aggregation was falling behind too because it was automatically picking indexes not best meant for it (i.e. it would pick one index for the date range, but it ended up doing a COLLSCAN for a second part of the query that was in another index.  It could have picked up the correct index that had the combination, but it didn't).  Thus, we removed all multi indexes and created one single index with all the key fields used across the reports.   Memory was reduced quickly and initially the index worked perfectly, but as our data grows we see this index starting to move VERY slowly.  

 

Is it possible to create an index that is used for hint only, where aggregation and other query/cursors do not pull from?



 Comments   
Comment by Michael Mikhjian [ 25/Oct/18 ]

Jumping on 15463 here, thank you!

Comment by Danny Hatcher (Inactive) [ 25/Oct/18 ]

Hello Michael,

Unfortunately, MongoDB does not currently support "hint-only" indexes. There is a feature request open for that concept as SERVER-15463. I highly encourage you to vote for that ticket as our Product team uses votes to inform feature prioritization in the future.

Thank you,

Danny

Generated at Thu Feb 08 04:46:54 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.