How to use Elasticsearch next to Mysql?

Question

I’m looking to use Elasticsearch to see if I can get agility and speed in my Mysql queries.

I started to see some things, but I found a little confusing the issue of integration with Mysql, someone has some experience with Elasticsearch and can explain how to integrate Elasticsearch with Mysql?

Answer 1

You may need to improve your question a little by putting more information such as the language in which your application was written (I’m talking about the application that currently accesses this database).

Nevertheless, I will try to give a general answer that I hope will help.

Elasticsearch is a distributed search server, REST, free/open source software based on Apache Lucene.

Now, to put it simply, imagine he’s a black box. You will just put the information you want to store and then you will be able to recover it with the id in a similar way to what you do with Mysql. The main advantage as you said yourself, is that you can do quick text searches (like google ;P).

Another difference is that you don’t have an SQL (and at first you don’t need a schema). To register items in Elasticsearch you simply submit a json via url (HTTP) with the information you want to save and search for later.

curl -XPUT 'http://localhost:9200/comidas/doce/1' -d '{
    "nome": "Sorvete",
    "descricao": "São deliciosos!",
}'

Similarly, when performing the search, just pass a url with what you want to search for (and maybe some options not worth discussing here.)

curl -XGET 'http://localhost:9200/comidas/_search?q=descricao:deliciosos'

# Resposta

{
   "took":14,
   "timed_out":false,
   "_shards":{
      "total":5,
      "successful":5,
      "failed":0
   },
   "hits":{
      "total":1,
      "max_score":0.19178301,
      "hits":[
         {
            "_index":"comidas",
            "_type":"doce",
            "_id":"1",
            "_score":0.19178301,
            "_source":{
               "nome":"Sorvete",
               "descricao":"São deliciosos!"
            }
         }
      ]
   }
}

The summary is that in the most direct solution, you have to save your information (for example, an employee’s data) in both mysql and Elasticsearch. This would be a responsibility of your application (the same one that uses mysql).

Similarly, when something changes in the bank (an employee has changed address), you also need to save that data in Elasticsearch.

A little boring, huh? To facilitate a little the work of those who need to have the information from somewhere automatically inserted also in Elasticsearch there are the Rivers, which are plugins that given a source, automatically "feed" the Elasticsearch.

There is a specific river for some databases which is the https://github.com/jprante/elasticsearch-river-jdbc .

With this plugin installed and configured in Elasticsearch, as your database is being changed the changes are being synchronized with Elasticsearch. So all you have to do is make the queries via HTTP in your application.

A tutorial (in English) on how to start using this plugin can be found at https://github.com/jprante/elasticsearch-river-jdbc/wiki/Quickstart

Although this cool plugin exists, I highly recommend you try to know a little more about Elasticsearch, and it is very useful to understand what the plugin is doing behind for you.

Most of the tutorials (and first steps) of Elasticsearch are in English, but I found those of the links in Portuguese that are cool.

• Introduction to Elasticsearch - Textual search in your application
• Introduction to Elasticsearch