CODEWITHSUNDEEP
javaperformancemongodb
prototypetolyfe
prototypetolyfe

Reputation: 199

MongoDB query performance issues

I have a contact list application using mongoDB to store the contacts and the java driver to interface with the database. Each contact is its own document with a number of fields, including GivenName, Surname, and MiddleInitial.

I recently added 150,000 additional contacts which slowed down performance. I added an index for Surname/GivenName/MiddleInitial (for sorting and searching by Surname) and one for GivenName (for searching by GivenName). This helped for the most part, except in a few cases. All of the searches are regular expressions anchored to the beginning of the string (e.g. ^Ale.*).

When searching by first name, queries that begin with q, u, x, or z perform noticeably slower than any other letter; searching by last name gets slower the closer to z the first letter is. I have not been able to find any other examples of this type of problem. Any help is appreciated.

EDIT:

Here are the indexes:

collection.ensureIndex(new BasicDBObject("Surname",1).append("GivenName",1).append("MiddleInitial",1));
collection.ensureIndex(new BasicDBObject("GivenName", 1));

and the queries:

BasicDBObject contactInfo = new BasicDBObject("GivenName", new BasicDBObject("$regex", "(?i)^al.*"); //GivenName may be Surname, al is just an example query

DBCursor cursor = collection.find(contactInfo).sort(new BasicDBObject("Surname",1).append("GivenName", 1).append("MiddleInitial", 1));

Explain results a-z on GivenName are here

Explain results a-z on GivenName without sort are here

Upvotes: 3

Views: 585

Answers (1)

evanchooly
evanchooly

Reputation: 6243

You're doing a case insensitive regular expression search. This will almost certainly bypass any indexes you have defined. One option is to store your fields twice with one copy force to upper/lower case then do your regex query against that. A starts-with query can still use an index but not if you're ignoring the case like that.

Upvotes: 2

Related Questions