My Index has a property named categoryId.My category Ids at the moment are [1,2,3] but there could be more categories like [1,2,3,4,5] in the future. Each record in my index can belongs to no categoryId , one categoryId or some categoryIds .
Currently I use a List<int> in my c# application as a list of categories for each record in my index.
This is a brief on how I index my records:
//some codes here
.Map<ItemElasticSearchViewModel>(m => m
.Dynamic(DynamicMapping.Strict)
.AutoMap()
.Properties(ps => ps
.Keyword(k => k.Name(f => f.itemCategories).Norms(false))
//some codes here
And at the moment I filter my records by category like this, for example I need records which categoryId=2 , so:
//some codes here
multiSearchResponse = await client.MultiSearchAsync(selector: ms => ms
.Search<ItemElasticSearchViewModel>("Fuzziness_0", s => s
.Index("itemindex")
.From(0)
.Size(15)
.Query(q => q
.Bool(b => b
.Filter(f => f.Term(t => t.itemCategories, "2"))
.Must(mu => mu
.Match(m => m
.Field(f => f.itemSearch)
.Query(searchViewModel.searchText)
.Operator(Operator.And)
)
)
)
)
//some codes here
Just focus on the Filter line. I need to know if there is a better way than what I have done, for filtering my searches by categoryId, cause at the moment I have about 200k records and I created a single record with categoryId=8 and query time has no difference with or without using Filter categoryId=8 . But in MySQL there is a significant difference in query time with or without categoryId filtering, cause when I use where categoryId=8 in MySQL, it passes all the records except the one and so fast !!
How do you Filter your record by category in elastic search when you have flexible categories and a record can have multiple categories??