Stumbled into one video for example, when the number of products in the categories for the menu got from elasticsearch:

'aggs' => [ 'group_by_category' => [ 'terms' => [ 'field' => 'categories' ] ] ]

Why is there elastic here if you can do with the count and group by functions in Mysql or postgres.

  • So what about the video as a whole? - de_frag
  • dy is a full course. The question is that all the same faster, elastic or DB? - Edward
  • Well, this is rather a demonstration of possibilities, and not that a straight elastic in this case is better or faster. It is clear that everything depends on the task. - de_frag 2:49 pm

1 answer 1

In short, ElasticSearch builds aggregations much faster than MySQL. And the more data, the more significant this difference.

I measured the execution time of requests on the combat server.

Server options

Intel (R) Core (TM) i7-4770K CPU @ 3.50GHz, RAM 30Mb, SSD MySQL 5.6, the number of records in the table - 250258 ElasticSearch 2.4, the same number of documents.

Mysql

 select brand_id, count(*) from product group by brand_id

Elasticsearch

 time curl -X POST "http://localhost:9200/my_index/product/_search?pretty=true" -d '{"size": 0,"aggs": {"group_by_brand": {"terms": {"field": "brand.id"}}}}'

lead time

MySQL - 55 ... 76ms, average 65.5ms

ElasticSearch - 19 ... 24ms, average 21.5ms

If the data were more than 1 million, the difference would be more significant.

From this we can conclude that the choice depends on the task. If you need to count the number of products in the menu categories and there are not too many of them (<100), then you can safely use MySQL. And if you need to build filters for parametric search, where the number of products is displayed for each filter value, then you should use ElasticSearch only.

Good article in a subject: Use the index, JSON! Aggregation performance