[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: |
|
||||||||
| 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 |