Databases
StackOverflow2013
Postsdbo
POHow do I troubleshoot and improve this slow running query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I troubleshoot and improve this slow running query?
    primarykey
    data
    text
    <p>I'm looking to fine tune a string search query that I am using on Mongo. In the SQL Server world, I'd like to believe I have a decent understanding of how indexes work and how to build proper indexes. I tried giving it a shot with Mongo, but, I don't believe that I'm not going about it the right way.</p> <p>My collection has roughly 4.3 million documents. The document structure looks like this:</p> <pre class="lang-javascript prettyprint-override"><code>{ "_id":{ "$oid":"527027456239d1212c07a621" }, "ReleaseId":2451, "Status":"Accepted", "Title":"Hard Rhythmic Motions", "Country":"US", "MasterId":"35976", "Images":[ { "Type":"primary", "URI":"http://api.discogs.com/image/R-2451-1117047026.jpg", "URI150":"http://api.discogs.com/image/R-150-2451-1117047026.jpg", "Height":307, "Width":307 }, { "Type":"secondary", "URI":"http://api.discogs.com/image/R-2451-1117047033.jpg", "URI150":"http://api.discogs.com/image/R-150-2451-1117047033.jpg", "Height":307, "Width":307 } ], "Artists":[ { "_id":2894, "Name":"DJ Hyperactive" } ], "Formats":[ { "Name":null, "Quantity":1 } ], "Genres":[ "Electronic" ], "Styles":[ "Hardcore", "Acid" ] } </code></pre> <p>I am executing a case insensitive search on one of the top-level document properties and on one of the nested document properties:</p> <pre class="lang-javascript prettyprint-override"><code>db.releases.find({$or: [{Title: new RegExp('.*mozart.*',"i")},{'Artists.Name': new RegExp('.*mozart.*',"i")}]}) </code></pre> <p>I tried creating an index; when I execute <code>.getIndexes()</code> I can see the index I created:</p> <pre class="lang-javascript prettyprint-override"><code>{ "v" : 1, "key" : { "Title" : 1, "Artists.Name" : 1 }, "ns" : "discogs.releases", "name" : "Title_1_Artists.Name_1" } </code></pre> <p>At this point I thought that I would be all set. However, the query ends up taking between 28 and 32 seconds to execute. I tried calling <code>.explain()</code> to get a little more insight:</p> <pre class="lang-javascript prettyprint-override"><code>{ "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 4098, "nscannedObjects" : 4292400, "nscanned" : 4292400, "nscannedObjectsAllPlans" : 4292400, "nscannedAllPlans" : 4292400, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 29, "nChunkSkips" : 0, "millis" : 29958, "indexBounds" : { }, "server" : "lambic:27017" } </code></pre> <p>From my limited knowledge of Mongo, this looks like a table scan which is why the query isn't performing very well. However, I don't know how to make this query better! I would expect that the index that I created to cover this query, but, that must not be the case.</p> <p>Now, the last thing I want to point out is that this is certainly not on the most robust server. The hardware specs (including CPU and RAM) are very limited. However, if my analysis is correct and I'm doing a table scan, there must be some performance improvements I can make on the Mongo side.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGolfWolf
    1. USJustin Helgerson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI can tell you it is the regexes that are the problem, they can't use an index
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. USSammaye
    2. CO@Sammaye - Even if I remove the wildcard at the beginning of both regular expressions I still don't get an index only query (according to `explain`). Although, it does look like the execution time drops ~6-8 seconds. Are there other changes I can make?
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. USJustin Helgerson
    3. COWhat if you test each clause of the or?
      singulars
      1. POHow do I troubleshoot and improve this slow running query?
      1. USSammaye
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload