[SERVER-1599] support ability to pass hint to update Created: 09/Aug/10  Updated: 03/Jun/22  Resolved: 19/Jun/19

Status: Closed
Project: Core Server
Component/s: Write Ops
Affects Version/s: 1.6.0
Fix Version/s: 4.2.0-rc3, 4.3.1

Type: New Feature Priority: Major - P3
Reporter: Scott Hernandez (Inactive) Assignee: Xin Hao Zhang (Inactive)
Resolution: Done Votes: 126
Labels: asya, ch, former-quick-wins, s3, storch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Depends
is depended on by DRIVERS-751 support ability to pass hint to update Closed
Documented
is documented by DOCS-12819 Investigate changes in SERVER-1599 Closed
Related
related to SERVER-26413 Hinting an incompatible partial index... Backlog
related to SERVER-7105 Please provide a way to use $hint for... Closed
related to SERVER-25716 multi-update in the presence of an er... Closed
related to SERVER-54824 Size estimate of UpdateOpEntry in mon... Closed
is related to SERVER-15802 Query optimizer should always use equ... Closed
is related to SERVER-4796 Add limit/max to remove command Backlog
is related to SERVER-7944 add index hint support for operations... Closed
is related to SERVER-2677 Allow count to work with query hints Closed
Backwards Compatibility: Fully Compatible
Backport Requested:
v4.2
Sprint: Query 2019-06-17, Query 2019-07-01
Participants:
Case:
Linked BF Score: 38

 Description   

Support a way to pass a hint to update command to use a specific index for update query filter. Work done under this ticket will not prevent misuse of hinting, as reported by SERVER-26413.

Original description:
Change the behavior on the server for update to look for the special fields: $query, $sortby, $limit, $hint. If these are found treat them the same as the Op_Query does.

This will allow sorting (so you can update the right element), $limit (so you can just update some of them), $hint (so you can force the right index) in addition to the normal $query part.

This should be backwards compatible. It will either be a $query + other $ options, or just the query, without options (like it is now).



 Comments   
Comment by Githook User [ 03/Jul/19 ]

Author:

{'name': 'Blake Oler', 'email': 'blake.oler@mongodb.com', 'username': 'BlakeIsBlake'}

Message: SERVER-1599 Fix lint

(cherry picked from commit cc59938cb51628b33403b19afd4cf9ba70c25921)
Branch: v4.2
https://github.com/mongodb/mongo/commit/3da9c08ac98e5a3761c68210ffb4416062cd1ec5

Comment by Githook User [ 03/Jul/19 ]

Author:

{'name': 'Xin Hao Zhang', 'email': 'xinhao.zhang@mongodb.com', 'username': 'xinhaoz'}

Message: SERVER-1599 Add support for index hint to update command

(cherry picked from commit 90da71501d23dc1b5a3d78417079fd62e989e035)
Branch: v4.2
https://github.com/mongodb/mongo/commit/0984d2f5318d54cb6b7691dee1a54fcd953c7357

Comment by Githook User [ 18/Jun/19 ]

Author:

{'name': 'Blake Oler', 'email': 'blake.oler@mongodb.com', 'username': 'BlakeIsBlake'}

Message: SERVER-1599 Fix lint
Branch: master
https://github.com/mongodb/mongo/commit/cc59938cb51628b33403b19afd4cf9ba70c25921

Comment by Githook User [ 18/Jun/19 ]

Author:

{'name': 'Xin Hao Zhang', 'email': 'xinhao.zhang@mongodb.com', 'username': 'xinhaoz'}

Message: SERVER-1599 Add support for index hint to update command
Branch: master
https://github.com/mongodb/mongo/commit/90da71501d23dc1b5a3d78417079fd62e989e035

Comment by Abde Faruqui [ 04/Apr/19 ]

+1 for hint

Comment by Asya Kamsky [ 28/Jan/19 ]

stuart.hall@masternaut.com this ticket is only meant to track ability to pass hint to an update command.

Ability to sort to update a specific single document is already possible in findAndModify and limiting updateMany would always break in a sharded cluster so we don't have any plans to add that. I will update the title and description of the ticket accordingly.

Comment by Stuart Hall [ 23/Jan/19 ]

We've hit an issue with a specific query that requires a $hint, and this issue is proving a pain to work around. We're currently using Index Filters as a work around, but their ephemeral nature (they don't survive a mongod restart), and lack of proper cluster support makes them challenging to work with in production.

Seems an odd thing to still not support update query hints (as well as the other requests in this ticket) over eight years after the initial report was created...

Comment by David López Pérez [ 28/Aug/18 ]

+1 for $limit also

Comment by Francesco Rivola [ 27/Aug/18 ]

+1 for $limit. UpdateMany and deleteMany in batches can take huge benefit of this feature.

Comment by Guy Margalit [ 14/Jun/18 ]

+1 for $hint

Comment by Evgeny Grin [ 19/Oct/17 ]

+1 for $hint

Comment by John Butler [ 13/Oct/16 ]

+1 for $sort. I have a case where I only want to update one object and so sorting matters.

Comment by eran [ 25/Dec/14 ]

@John Myers :
We had the same issue and solved it by sorting the query and then limiting the update to all document that are below a sort threshold.
This give estimated results at best... but only require 1 query and 1 update (also it will only work if the sorted field is quite unique).
For example (not actual code):

var horizon=5000;
do {
  var query={....};
  var last = db.collection.find(query).sort({sortkey:1}).skip(horizon-1).limit(1)[0];
  
  if(last) {
    query['sortkey'] = {$lte : last['sortkey'] };
    db.collection.update(query,{$set:{mark:"jabberwocky"}},{multi:1});
    break;
  }
 
  horizon =  Math.floor( horizon / 2 );
} while (horizon);

In most cases it would not iterate (also if you only care about updating once you reached your limit you can simply remote the iteration)

Hope this helps....

Comment by John Myers [ 24/Dec/14 ]

+1 for $limit. HUGE for batch processing which someone mentioned (2 years ago...). I'm at a wall right now, having to do a batch query with $limit, and then iterate over all the results to set my flag for documents within the batch is insanely inefficient, especially when my client program is the one that has to do the heavy lifting.

Comment by Roman Skvazh [ 12/Nov/14 ]

Index Filters helps for us! But they are not persistent. Mongod uses wrong index sometimes (when it in ram?) when updates occurred.

Comment by Kevin Pulo [ 29/Oct/14 ]

A potential workaround for $hint on updates in MongoDB 2.6 is to instead use Index Filters.

Comment by Bill Perkins [ 28/Apr/14 ]

Limit would be very useful for us. We have many producers and consumers, and need to assign work to one and only one consumer in batches. It would be great to be able to do an update with limit(batch size).

Comment by aurelien lambert [ 23/Apr/14 ]

I just had a case where $hint is vital for updates because MongoDB keep using a sparse index and cannot update the documents where the indexed field does not exist

Comment by rgpublic [ 17/Jan/14 ]

I just wanted to add my "+1" here because I just stumbled over this. My use case: A huge number of rows needs to be updated with a certain flag. I'd like to do this in a very controlled way - only doing 1000 rows at a time and then giving other queries enough time to complete before doing 1000 rows again. This is especially necessary because of MongoDB's currently somewhat limited concurrency features. Unfortunately $limit doesnt work with updates so the only approximate solution is to have MongoDB update all rows and then kill the operation after some time. Ugh.

Comment by Jon Keys [ 26/Apr/13 ]

I'd also love to have this feature. Working around it is proving to be quite painful.

Comment by Ilya Shaisultanov [ 09/Jan/13 ]

$limit for update() would be awesome. I actually didn't realize there was no support for it until I tested the code I wrote and saw that "limit" in update options doesn't do anything.

Comment by Samuele Artuso [ 04/Jul/12 ]

It would definitely useful to have these features in Mongo.

Comment by Shane R. Spencer [ 18/Mar/12 ]

I agree on limit() for multi=true updates. This would be especially useful when doing batch processing. There are many many many other ways of handling this situation. Doing a limited update is one of the most direct and atomic methods of tagging a limited set of documents for later retrieval by that tag.

Example: Using a new UUID as a tag and modifying 20 documents that don't already have a 'batchtag'.

'batchtag' can be sparse indexed as well for very fast access to this small set.

Comment by Scott Hernandez (Inactive) [ 05/Oct/10 ]

The same should work for remove as well.

Comment by Scott Hernandez (Inactive) [ 10/Aug/10 ]

In the case where you don't use multi, sortby lets you chose which one you want to update, since it will update the first element only. For example if I were updating the best/wort sales-person I would need to be able to sort (else the sort will come from the index and could be wrong, or change ind. from the update statement).

In the case of limit, I might just want to update the top 20 sales-people to give them a bonus, or something.

All of these scenarios depend deeply on the structure of the data, but I want the query part of update to behave as much like find as possible.

As Aaron pointed pointed out I can do that with two queries (first to get the _ids, then using $in _id to update) but it would be nice if the query portion of update was compatible with the params for find.

Comment by Eliot Horowitz (Inactive) [ 10/Aug/10 ]

I understand $query and $hint.
Why would you want $sortby and $limit on an update?

Generated at Thu Feb 08 02:57:29 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.