2

query using normal indexMain problem I am facing is that I have created $text index with two fields with weights and not able to text search using that index. The result of the query is taking so long even after the text index is created.

created text index

text search in db Explain Plan

As you can see in the Explain Plan 'Query used the following index' is not working for this query (green tick mark should be there if the index is being used, right?). So my query is checking each and every document rather than using text index created.

Please let help with this.

Prathyusha Prathyu
  • 21
  • 4
  • 1
    A text index will only be used if you are querying with the `$text` operator. Can we see an example of your query? – Joe Apr 07 '20 at 22:28
  • Here it is. In MongoDB compass ExplainPlan: {$text:{$search:{'query'}}} in the filter {$project:'-id':0, 'pop':1} Query used the following index: must use the compound index which is not the case. – Prathyusha Prathyu Apr 08 '20 at 07:33
  • so your problem is that your query is not being covered by your text index right? send us a screenshot of the explain plan, with your search term. – Shashwat Vinod Singhal Apr 08 '20 at 08:00
  • That explain plan shows that your query returned examined 100 index keys, then 100 documents, and then returned 100 documents. So it is definitely using the index, and only scanned the minimum number documents required to process this query. – Joe Apr 08 '20 at 16:47
  • But the problem is the execution time, just have a look image of a query I added were it is actually using a normal index for search. The same way it should tell us that it is using text search. Using text search in 11million documents, it's taking forever for me. I added the code snippet too. @Joe – Prathyusha Prathyu Apr 08 '20 at 20:42
  • There is no other way for a `$text` search to be processed. If a text index does not exist on the collection, it would just fail. The text filter you are using is roughly equivalent to `{$or:[{name_ngrams:{$regex:"dil"}},{name_ngrams:{$regex:"to"}},{all_ngrams:{$regex:"dil"}},{all_ngrams:{$regex:"to"}}]}`. How does the performance of this query compare to the text search? – Joe Apr 08 '20 at 23:16
  • I have added an image where normal index is being used and it is specified in the query usind the following index section. i also added code of the pipeline which w=I used for query the db – Prathyusha Prathyu Apr 09 '20 at 00:13
  • if $text index doen't exist then definitely if there is no exact match we cannot fetch any result, completely agreed and to use text search on docs, text index must be there i agree. But the process must be fast, it shouldn't be this slow. – Prathyusha Prathyu Apr 09 '20 at 00:18
  • pipeline = [{'$match':{'$text': {'$search': 'love you zindagi'}}}, {'$sort': {'score': {'$meta': "textScore"}}}, {'$limit':3}, {'$sort':{'popularity':-1}}, {'$limit':4}, {'$project': {'_id':0,'name': 1,'popularity':1}}, ] res= collection.aggregate(pipeline) print(list(res)) this is the pipeline am running over 11m documents and it's taking so long which shouldn't – Prathyusha Prathyu Apr 09 '20 at 00:22

0 Answers0