CODEWITHSUNDEEP
elasticsearchelasticsearch-2.0
Marvin Saldinger
Marvin Saldinger

Reputation: 1400

MySQL query to Elasticsearch query

I'm trying get the best performance for an Elasticsearch query.
I need something similar to this MySQL query: 

SELECT * FROM table WHERE field_a IN ('value1', 'value2') AND field_b = value

The number of values for field_a may be between 1 and let's say 20. All fields require an exact match.

Right now I'm using query: 

{
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "should" : [
                 { "term" : {"field_a" : "value1"}}, 
                 { "term" : {"field_a" : "value2"}} 
              ],
              "must" : {
                 "term" : {"field_b" : "value"} 
              }
           }
         }
      }
   }
}

The index has at this point around 30 million documents and this query returns the result in 0.04s - 0.06s (via cURL).
Is there a better way to get the results I need?

Update
The field_a is a string not analyzed.
The field_b is an integer.

Upvotes: 2

Views: 726

Answers (2)

Avihoo Mamka
Avihoo Mamka

Reputation: 4786

If you have a list of values you want field_a to compare against, you can use the following query:

{
   "query" : {
      "constant_score" : { 
         "filter" : {
            "bool" : {
              "should" : [
                 { "terms" : {"field_a" : ["value1", "value2", "etc..."]}} 
              ],
              "must" : {
                 "term" : {"field_b" : "value"} 
              }
           }
         }
      }
   }
}

Upvotes: 2

Ruslan Borovok
Ruslan Borovok

Reputation: 530

Indexes must be created for field_a and field_b. Do not use * in query - it reduces performance. Instead * - write the fields you need.

Upvotes: 1

Related Questions