Full text searching performance (compare to mysql)

Elastic Stack Elasticsearch
1

I am trying to use elasticsearching over mysql as my searching is slow, so i am just making demo and i am finding mysql performing better.

But i doubt if i am do something wrong

Till now what i have done:-

localhost:9200/elastic_analysis_patternl/_settings

{
   "elastic_analysis_pattern": {
       "settings": {
           "index": {
               "creation_date": "1459959233955",
               "analysis": {
                   "filter": {
                         "edge_ngram": {
                            "min_gram": "2",
                             "side": "front",
                             "type": "edgeNGram",
                             "max_gram": "20"
                          }
                    },
                   "analyzer": {
                        "pattern_index": {
                                 "filter": [  "lowercase",  
                                                 "stop",  
                                                 "edge_ngram"   
                                              ],
                                             "tokenizer": "pattern_token"
                                   },
                                  "pattern_search": {
                                             "filter": [   "lowercase",
                                                               "stop"   ],
                                           "tokenizer": "whitespace_token"
                                  }
                         },
                         "tokenizer": {
                                 "whitespace_token": {
                                        "type": "whitespace"
                                   },
                                 "pattern_token": {
                                      "pattern": "[^\\p{L}\\d]+",
                                      "type": "pattern"
                                  }
                          }
          },
         "number_of_shards": "1",
         "number_of_replicas": "0",
         "uuid": "109pq9KfTVq32rIydKMPtg",
         " version": {
               "created": "2020199"
         }
     }
   }
 }
}

localhost:9200/elastic_analysis_pattern/_mapping/

{
  "elastic_analysis_pattern": {
      "mappings": {
         "search_analysis": {
               "properties": {
                      "search": {
                              "type": "string",
                             "analyzer": "pattern_index",
                             "search_analyzer": "pattern_search"
                      }
                }
           }
     }
  }
}

query i am searching is say:-

{
  "query" : {
          "bool" : {
                    "must"  : {
                           "match" :  {
                                   "search" : "param1 param2"
                             }

                    }
                }
      },
    "size"  => 10
}

and mysql query is:

SELECT SQL_NO_CACHE * FROM internet_ads WHERE (MATCH (search) AGAINST (' +sho' IN BOOLEAN MODE)) LIMIT 0,10*

i am testing with database having around 60,000 row

exectution time is*

elastic time = 0.16344904899597
mysql time = 0.070924997329712

Can you please let me know if i am wrong here. Its already one week since i started looking into elastic search and started feeling like i wasted my time here.

2

Does number of rows matter in performance compare to mysql?

3

Not really. So if you index 1 million of docs and you compare with elasticsearch, you should see a difference.

How many docs you have so far?

4

@dadoonet
right now for demo i am trying with ~50000 doc, but in real i have 500000 docs

5

So try with 500000 docs. It does not take too long to inject that in Elasticsearch. You will get a better knowledge on the response time.

6

ok will check with that too

7

Are you running elasticsearch with basic install?
What configuration mysql is running?
For large data you may be better with the more number of shards.

1 Like
8

Yeah, 50,000 docs is nothing, at a number that low any difference you see is more or less noise anyway.