How can I query a MongoDB collection by both a geo spatial index and a text index quickly?

Charlie Schliesser

Given the collection locations consisting of ~20,000,000 documents with 3 properties:

{
    _id,
    name, // string
    geo // coordinate pair, e.g. [-90.123456, 30.123456]
}

and an index of name: 1 and a geo index setup like so:

{ 
    "geo" : "2dsphere"
},
{ 
    "v" : 1, 
    "name" : "geo_2dsphere", 
    "ns" : "db.locations", 
    "min" : "-180.0", 
    "max" : "180.0", 
    "w" : 1.0, 
    "2dsphereIndexVersion" : 2
}

How can I performantly query against this collection both on the geo_2dsphere index and on the name index?

When I run a $box query on the geo index only, it takes over 20 seconds to return 50 results. When I include a search against the name property it goes up even further.

If I run a $near query, then things can perform very quickly, but sometimes queries seem to (very randomly) go from ~200ms to many seconds. See this example where the only difference is one additional character on the name index which actually increases the time:

200ms:

{name: /^mac/, geo: {$near: {$geometry: {type: "Point", coordinates: [ -90.123456, 30.123456 ]}, $maxDistance: 20000}}}

18,000ms:

 {name: /^macy/, geo: {$near: {$geometry: {type: "Point", coordinates: [ -90.123456, 30.123456 ]}, $maxDistance: 20000}}}

I can't understand why being more specific with an index is slowing things down so much. When I get more specific with a phrase, I have to drastically reduce the $maxDistance to something like 7,000 meters before the query returns in any reasonable amount of time.

Is there a better setup I should be doing here?

Charlie Schliesser

As has been pointed out to me by Blakes Seven, you cannot search across multiple indexes in MongoDB:

There is a "highlander rule" (there can be only one) in the query evaluation that denies the usage of more than "one" "special" index in a query evaluation. So you cannot have multiple "text" or muliple "geospatial" or any combination of "text" and "geospatial" or usage of any of those within an $or condition, that results in multiple index selection.

So, I've opted to move over to Elasticsearch for this specific query, indexing only what I need to complete these multi-index queries, and then use those results to load the necessary Mongo documents. Works quickly, works well.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Why can I index a key in both ascending and descending order in MongoDB?

From Dev

How do I add to a spatial index in OrientDB?

From Dev

MongoDB spatial query - error unable to find index or no results

From Dev

How can I add an item into a Laravel Eloquent Collection by index?

From Dev

How to combine text and geo query using Spring Data MongoDB?

From Dev

mongodb : How do I create text index with C# driver?

From Dev

How do I create a text index in mongodb with golang and the mgo library?

From Dev

GEO2D index implementation in MongoDB

From Dev

How can I create an index in on an array field in MongoDB?

From Dev

How can I define index on inherited fields in MongoDB with Spring Data?

From Dev

can i have shard collection without index

From Dev

Can I loop part of a collection based on index?

From Dev

In SQL how can I use an index to speed up this database query?

From Dev

How can i optimize MySQL query, event if it have index

From Dev

How can I query a postgreSQL Table that uses an index?

From Dev

How can I make a query force to use such an index?

From Dev

Lucene geo spatial ring query

From Dev

In a table partitioned BY LIST (n), how can I quickly SELECT MAX(n) without an index?

From Dev

In a table partitioned BY LIST (n), how can I quickly SELECT MAX(n) without an index?

From Dev

Mongoosastic, how can i make an Geo Distance Query

From Dev

mongodb: How to use an index for distinct command and query?

From Dev

How to create index for this particular query in MongoDB

From Dev

How to cast a MongoDB query and use Index

From Dev

How can I store the result of MongoDB query in a text file with Nodejs?

From Dev

Query a named text index on a collection in mongo using mongoose

From Dev

How can I get index of first integer from specific starting index in text file in Java?

From Dev

MongoDB $or query with index

From Dev

How to create unique index in MongoDB that works for both upper and lower case?

From Dev

Mongodb: How to index multiple nested text fields?

Related Related

  1. 1

    Why can I index a key in both ascending and descending order in MongoDB?

  2. 2

    How do I add to a spatial index in OrientDB?

  3. 3

    MongoDB spatial query - error unable to find index or no results

  4. 4

    How can I add an item into a Laravel Eloquent Collection by index?

  5. 5

    How to combine text and geo query using Spring Data MongoDB?

  6. 6

    mongodb : How do I create text index with C# driver?

  7. 7

    How do I create a text index in mongodb with golang and the mgo library?

  8. 8

    GEO2D index implementation in MongoDB

  9. 9

    How can I create an index in on an array field in MongoDB?

  10. 10

    How can I define index on inherited fields in MongoDB with Spring Data?

  11. 11

    can i have shard collection without index

  12. 12

    Can I loop part of a collection based on index?

  13. 13

    In SQL how can I use an index to speed up this database query?

  14. 14

    How can i optimize MySQL query, event if it have index

  15. 15

    How can I query a postgreSQL Table that uses an index?

  16. 16

    How can I make a query force to use such an index?

  17. 17

    Lucene geo spatial ring query

  18. 18

    In a table partitioned BY LIST (n), how can I quickly SELECT MAX(n) without an index?

  19. 19

    In a table partitioned BY LIST (n), how can I quickly SELECT MAX(n) without an index?

  20. 20

    Mongoosastic, how can i make an Geo Distance Query

  21. 21

    mongodb: How to use an index for distinct command and query?

  22. 22

    How to create index for this particular query in MongoDB

  23. 23

    How to cast a MongoDB query and use Index

  24. 24

    How can I store the result of MongoDB query in a text file with Nodejs?

  25. 25

    Query a named text index on a collection in mongo using mongoose

  26. 26

    How can I get index of first integer from specific starting index in text file in Java?

  27. 27

    MongoDB $or query with index

  28. 28

    How to create unique index in MongoDB that works for both upper and lower case?

  29. 29

    Mongodb: How to index multiple nested text fields?

HotTag

Archive