ElasticSearch vs SQL Full Text Search [closed]

Question

I want to use full text search in my project... Can anyone explain me, what is the difference between ElasticSearch and SQL Full Text Search

Or

why SQL Full Text Search is better (worse) than elastic?

documentations, presentations, schema...

Himself

Answer 1

Define "better"... sql full text search is fairly trivial to get working (indexing and query) - but it has penalties:

• very little (virtually no) control over how things are indexed (what the index keys are; what the lexers/stemmers/etc are; etc)
• runs on the sql server - which is usually your least scalable infrastructure

Elastic search requires more work; you need to setup and maintain a dedicated cluster of nodes, and then provide code that performs the actual index operations, which may also involve a scheduled job that works from a change-log (processing new / edited data), building the fragments to be indexed; equally, you need to then take more time building the query. But you get a lot of control over the index and query, and scalability (a cluster can be whatever size you need). If it helps any, Stack Overflow grew up on sql full text search, then moved into elastic search when the limitations (both features and performance) proved prohibitive.

Limy

some feedback : nitschinger.at/… – Klotz

@rudibruchez I'm not sure that is "feedback" as such, but it is an interesting and relevant link – Limy

You're right, I said feedback because the author is commenting on jTDS vs the ms JDBC driver, but it's just a sentence in the article. It's mostly tutorial. Just taking the opportunity : Shpinx is a C++ contender : sphinxsearch.com/about/sphinx – Klotz

stackoverflow starts with Lucene.NET and then moved to elasticsearch – Shortcoming

@Shortcoming well, we weren't using lucene for very long, so I didn't bother mentioning it – Limy

Answer 2

The answer depends on what goal you're trying to achieve and what resources you have to reach it. SQL server fulltext search is lower admin but limited in functionalities. Elastic search is at the other end of the spectrum.

SQL server fulltext search:

• can prove efficient if you're data is not considerable growing and or schema is not changing over time
• requires less effort to maintain and less of a learning curve/need for new competence

Elasticsearch:

• need for data ingestion if your master db is having frequent incremental updates (logstash and other alternatives)
• scales better horizontally
• ability to use advanced features to improve performance for very large data sets (eg. routing)

Scalping