Performance: Calculations inside query, ES vs Mysql

Elastic Stack Elasticsearch
1

Calculation in select query which is faster, mysql or elastic search?

{
"query" : {
...
},
"script_fields" : {
"gross_profit" : {
script: "((((doc['price_in_cents'].value * 100)/(100 + 15)) - doc['cost_in_cents'].value)*(doc['quantity'].value)) "
}
}
}

or

SELECT ((price_in_cents*100)/100/(100+15)) - cost_in_cents * quantity) as gross_profit FROM ...

2

This comparison is not very fair. Mysql is designed to return all matches while elasticsearch is designed to only return the top matches. For instance in this example, mysql will run the computation on all documents while elasticsearch will only do it on the top matches that are returned (10 by default).

3

Although you could LIMIT 10 on MySQL, then it'd be more an apples to apples comparison. However, over 10 items it probably doesn't matter

4

Are you trying to choose between MySQL and Elasticsearch? Honestly, I think there's bigger considerations than whether or not this particular use case is going to be faster/slower.

Choose Elasticsearch if

  • You're ranking something (ie your formula factors into relevance ranking or some other kind of similarity)
  • You're matching on text or something else that can be tokenized
  • Your data model is simple to denormalize into documents
  • You just want an analytics interface, not a system of truth

Choose MySQL (or any RDMBS) if:

  • Your data cannot be easily denormalized
  • You don't want to load data from a system of truth into a search engine
  • You don't care about text matching
  • You don't care about controlling ranking other than some simple SORTing
4 Likes