php - ElasticSearch usage with MySQL

Question

I'm using ElasticSearch for the search component of a site. The data that is being indexed and eventually searched is the same data that is being saved in a MySQL DB.

My approach to this is to add/delete/modify data in the index when the corresponding CRUD MySQL operation happens.

For instance, a create operation looks something like this:

public function savePost(Request $request) {
    //Firstly, create the object and save it to MySQL
    $post = new Post();
    $post->title = $request->title;
    $post->body = $request->body;
    //...
    //and so on
    $post->save();

    //Secondly, index this new data:
    $elasticSearchClient = ClientBuilder::create()->build();

    $params = [
        'index' => 'some_index_elasticsearch',
        'id' =>  $post->id,
        'type' => 'post',
        'timestamp' => time(),
        'body' => [
            'id' => $post->id,
            'title' => $post->title,
            'body' => $post->body,
            //... and so on
        ],
    ];

    $elasticSearchClient->index($params);

}

If the data is deleted/updated in MySQL I'd just delete it or update it from the index.

Is this the right approach to using MySQL with ElasticSearch (or any other comparable technology like Sphinx)? or would you recommend a better approach to using MySQL as a more of a data source for ElasticSearch? (which really isn't happening at all here because there is no interaction between ElasticSearch and MySQL at all).

I'm using https://github.com/elastic/elasticsearch-php to interact with ElasticSearch if it makes any difference.

Just to clarify: this approach does work so far - I'm just not sure if it is the right way, or if anyone can see problems that I may run into with this way of doing things.

Answer 1

ElasticSearch does not fit very well for updating/deleting documents in large scale.

There are many aproaches that try to minimize the overloading about this downside on it's architecture, but if think that increases the complexity of your solution.

I suggest that you keep CRUD operations only on MySQL and use ES as append-only. Actually, StackOverflow itself, and many others great TI companies uses this approach.