0

I need to execute the following query:

db.S12_RU.find({"venue.raw":a,"title":/b|c|d|e/}).sort({"year":-1}).skip(X).limit(Y);

where X and Y are numbers.

The number of documents in my collection is:

208915369

Currently, this sort of query takes about 6 minutes to execute.

I have the following indexes:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_"
    },
    {
        "v" : 2,
        "key" : {
            "venue.raw" : 1
        },
        "name" : "venue.raw_1"
    },
    {
        "v" : 2,
        "key" : {
            "venue.raw" : 1,
            "title" : 1,
            "year" : -1
        },
        "name" : "venue.raw_1_title_1_year_-1"
    }
]

A standard document looks like this:

{ "_id" : ObjectId("5fc25fc091e3146fb10484af"), "id" : "1967181478", "title" : "Quality of Life of Swedish Women with Fibromyalgia Syndrome, Rheumatoid Arthritis or Systemic Lupus Erythematosus", "authors" : [ { "name" : "Carol S. Burckhardt", "id" : "2052326732" }, { "name" : "Birgitha Archenholtz", "id" : "2800742121" }, { "name" : "Kaisa Mannerkorpi", "id" : "240289002" }, { "name" : "Anders Bjelle", "id" : "2419758571" } ], "venue" : { "raw" : "Journal of Musculoskeletal Pain", "id" : "49327845" }, "year" : 1993, "n_citation" : 31, "page_start" : "199", "page_end" : "207", "doc_type" : "Journal", "publisher" : "Taylor & Francis", "volume" : "1", "issue" : "", "doi" : "10.1300/J094v01n03_20" }

Is there any way to make this query execute in a few seconds?

3
  • You already have a proper composite index for you query so I guess better option would be to look into the winning plan and see what is causing that much time and also try have a look into performance of using regex MongoDB, performance of query by regular expression on indexed fields Also a suggestion, please edit your code properly so it is more readable. Commented Jan 5, 2021 at 5:22
  • @vikscool tq.so sorry I am not fully experienced with mongodb. only basic knowledge. may I ask If there's an article etc. you can point to that can guide me on how veto optimise by looking the winning plan? Commented Jan 5, 2021 at 7:06
  • there are different blog posts/articles available on the same topic but if you need proper understanding i would suggest looking into the mongodb courses at MongoDB University it's free there I would suggest to take the M121 course it will help you in understanding a bit more. For now you can check the winning plan by adding .explain() after your query Commented Jan 5, 2021 at 7:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.