[SERVER-24913] Remove index restriction on $or clauses for $text searches Created: 05/Jul/16 Updated: 16/Jun/21 Resolved: 28/Jul/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Billy Tetrud | Assignee: | Unassigned |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||
| Participants: | |||||||||||||
| Description |
|
Right now, the docs say that $text searches have the following restriction:
To get around this restriction, you have to do two separate queries and merge them together. This complicates things unnecessarily. Why is this restriction there? I propose it be removed. |
| Comments |
| Comment by Jerome Mutgeert [ 16/Jun/21 ] |
|
Hi all in trouble, I've got a decent workaround solutuion:
Given that I have an index on the column: db.Products.find({$or:[ {name:{$regex:'hi', $options:'i'}, created: {$ne: 'nonExistingString'}}, {$text: {$search: 'Hi'}} ]}) |
| Comment by Kelsey Schubert [ 28/Jul/16 ] |
|
Hi fresheneesz, Thanks for the additional details about your use case. I'm closing this ticket as a duplicate of SERVER-13803, please feel free to vote for it and watch it for updates. Kind regards, |
| Comment by Billy Tetrud [ 06/Jul/16 ] |
|
" If the dataset is small,...; if the dataset is large ..." You're missing a case: when the dataset is large but is reduced by an query condition that constrains the dataset to a small subset. An index on the whole collection would be expensive and unnecessary for searches where an $and condition limits the number of records to something small. I'm doing this as a workaround for the limitations of Mongo's $text searching. $text searching can't currently return partial-word matches, and so i use the $text index to search most properties, but a couple more important queries, I use a $regex condition to get partial-word matches. It would be nice if $text indexes were more flexible, or if there were customizable indexes. But for now, it seems I need to do it this way.
Ah, so what's the root of this limitation. It seems like an $or condition should be able to use indexes in a piecemeal way. At worst, two searches can be run, and the results can be merged (just like my workaround does). |
| Comment by Ramon Fernandez Marina [ 06/Jul/16 ] |
|
Here's the exact text from the documentation:
While I see how this can be a limitation, I'm not sure the use case for mixing indexed queries and collection scans is a very good one. If the dataset is small, adding an index on the relevant fields will be cheap; if the dataset is large, I would not want to do a collection scan and I'd rather add an index on all the relevant fields instead to make my queries go faster. Can you add more details on your use case so we can better understand how this limitation is affecting you? Thanks, |