Elasticsearch Difference Between Relational Databases and Elasticsearch Usecases where Relational Databases are not suitable


Example

  • Essence of searching lies in its order. Everyone wants search results to be shown in such a way that best suited results are shown on top. Relational database do not have such capability. Elasticsearch on the other hand shows results on the basis of relevancy by default.

    Setup

    Same as used in previous example.

    Problem Statement

    Suppose user wants to search for shirts but he is interested in red colored shirts. In that case, results containing red and shirts keyword should come on top. Then results for other shirts should be shown after them.

    Solution Using Relational Database Query

    select * from product where name like '%Red%' or name like '%Shirt%';

    Output

    name       | id 
    -----------+----
    Shirt      |  1
    Red Shirt  |  2
    

    Elasticsearch Solution

    POST test/product/_search
    {
         "query": {
              "match": {
                "name": "Red Shirt"
             }
         }
    }
    

    Output

    "hits": [
      {
         "_index": "test",
         "_type": "product",
         "_id": "AVzglFomaus3G2tXc6sB",
         "_score": 1.2422675,              ===> Notice this
         "_source": {
            "id": 2,
            "name": "Red Shirt"
         }
      },
      {
         "_index": "test",
         "_type": "product",
         "_id": "AVzglD12aus3G2tXc6sA",
         "_score": 0.25427115,             ===> Notice this
         "_source": {
            "id": 1,
            "name": "Shirt"
         }
      }
     ]
    

    Conclusion

    As we can see above Relational Database has returned results in some random order, while Elasticsearch returns results in decreasing order of _score which is calculated on the basis of relevancy.


  • We tend to make mistakes while entering search string. There are cases when user enters an incorrect search parameter. Relational Databases won't handle such cases. Elasticsearch to the rescue.

    Setup

    Same as used in previous example.

    Problem Statement

    Suppose user wants to search for shirts but he enters an incorrect word shrt by mistake. User still expects to see the results of shirt.

    Solution Using Relational Database Query

    select * from product where name like '%shrt%';

    Output

    No results found
    

    Elasticsearch Solution

    POST /test/product/_search
    
     {
        "query": {
          "match": {
            "name": {
              "query": "shrt",
              "fuzziness": 2,
              "prefix_length": 0
             }
          }
        }
     }  
    

    Output

     "hits": [
      {
         "_index": "test",
         "_type": "product",
         "_id": "AVzglD12aus3G2tXc6sA",
         "_score": 1,
         "_source": {
            "id": 1,
            "name": "Shirt"
         }
      },
      {
         "_index": "test",
         "_type": "product",
         "_id": "AVzglFomaus3G2tXc6sB",
         "_score": 0.8784157,
         "_source": {
            "id": 2,
            "name": "Red Shirt"
         }
      }
    ]
    

    Conclusion

    As we can see above relational database has returned no results for an incorrect word searched, while Elasticsearch using its special fuzzy query returns results.