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?

forgodsakehold
  • 870
  • 10
  • 26
  • 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](https://stackoverflow.com/a/33219393/2417602) Also a suggestion, please edit your code properly so it is more readable. – vikscool Jan 05 '21 at 05: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? – forgodsakehold Jan 05 '21 at 07: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](http://university.mongodb.com/) *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 – vikscool Jan 05 '21 at 07:57

0 Answers0