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.