Should Areas (country, province, city, district) fields inside Listing collections stamped as index?

Working with Data
1

Hi there,

I’m just starting a fresh project and recently migrated (new) to MongoDB. The project is about an area-based listing directory where user can find a listing based on a country, province, city, and district level.

So in order to do so, both in Area and Listing collections will have the following field structure.

on Listing document:

area: {
  country: "us",
  province: "california",
  city: "san-fransisco",
  district: "bayview"
}

Again, the question is about should I stamp those fields as indexes? so that whenever we query the listings based on country or province or city or district level, the querying performance is faster.

Thanks

1 Like
2

Hi @ibgpramana

Welcome to MongoDB community.

Of course if those fields are your predicates you should index them for fast search.

However, the way you index them can depend on nature of queries.

If your query always include all fields you need to create a compound key in order of fields based on Equility Sort and Range rule order.

Now if your query can have any of the fields placed as a standalone you might need an index for each field seperately or a wild card index on area.$**.

Having said that there might be a good trick on autocomploting the predicates to force a compound index optimal scan. For example if we search only for sanfransisco on app side we can add us and california to the search as those make sense.

Thanks
Pavel

1 Like
3

Thank you for your help, Mr Pavel.

I was using MySQL and a table listings which had area_id as foreign key referenced to areas table where it was structured in Nested Set form.

I don’t think my query always include all fields, let say a user only search for listings in san-fransisco, then the query will only look for area.city == city:san-fransisco. Same if a user only asked to see listings in US, the query would only look for area.country == us.

as I stored the areas in each listing document as an array, I think I should create multi-key indexes on listing collections, for example:

  • area.country
  • area.province
  • area.city
  • area.district

I read them here:

Multikey Indexes — MongoDB Manual

4

Hi @ibgpramana,

I am not certain a multikey on a nested document will be effecient as on arrays of subdocuments where you index single fields

Anyway if you expect one predicates per query you might use seperate or wild card indexes on area.

Multikey index will be performant only if you use elemMatch on area…

Thanks
Pavel

1 Like
5

Hi Mr @Pavel_Duchovny,

I do think that we only expect one predicate per query, as an example, we’re only searching or retrieving listing based on a country, or province, or city, or district level

so as you say that we might use separate indexes on Area, you mean the single field index?

In summary, we just stamp the area field as indexes. like what the document says in Create an index on embedded document section: Single Field Indexes — MongoDB Manual

Please confirm, and thanks for your opinion/answer. It really helps.