3 ms·

so when should you use elasticsearch? can't you get away with doing SELECT id FROM pages WHERE title LIKE "%elastic"

josteink 12y ago
The search you mentioned there specifically is problematic on any large data-set, because in its current(ly unoptimized) state it wont be able to use index-seeking to arrive at an answer. This means that at best it will need to do a index-scan (in practice a full table-scan on the index consisting of a subset of the table-data). The results of a query like that can't be obtained fast, at least not milliseconds fast, and its not well suited for caching or reusability. There are (obviously) ways around this, but they all involve indirection and workarounds. Anyway, as a rule of thumb: Any time you want to do general full-text search queries on relational data, the LIKE operator will very quickly meet its limits and depending on how much outside its bounds you want to go, you will find yourself having to do quite a bit of work. And in those cases you mihgt want to use the full-text capabilities of your RDBMS if it has any. Or maybe something like Elasticsearch might be a better fit. Disclaimer: I've never used Elasticsearch myself.
frankwiles 12y ago
Those sorts of SQL queries aren't very fast compared to a dedicated index/search system like ElasticSearch. Especially when millions of rows are concerned. Think of search engines like ElasticSearch and Solr as being purpose built for "search" rather than ad hoc querying. They offer more advanced searching features like faceting and synonyms, if your example had been "SELECT id FROM pages WHERE title LIKE '%dog'" you could set things up so that matches for 'dog', 'dogs', 'doggie', 'puppy', 'pup', 'canine', and 'mans best friend' all returned the same results.
- jobposter1234 12y ago
  While you are absolutely correct that this won't scale, and doesn't have a lot of advanced features like faceting and synonyms, it's still a useful technique. I use the SQL LIKE operator all the time, especially when I just need a simple way of searching my data. If you under a million rows, LIKE is reasonably performant.

The search you mentioned there specifically is problematic on any large data-set, because in its current(ly unoptimized) state it wont be able to use index-seeking to arrive at an answer. This means that at best it will need to do a index-scan (in practice a full table-scan on the index consisting of a subset of the table-data). The results of a query like that can't be obtained fast, at least not milliseconds fast, and its not well suited for caching or reusability. There are (obviously) ways around this, but they all involve indirection and workarounds. Anyway, as a rule of thumb: Any time you want to do general full-text search queries on relational data, the LIKE operator will very quickly meet its limits and depending on how much outside its bounds you want to go, you will find yourself having to do quite a bit of work. And in those cases you mihgt want to use the full-text capabilities of your RDBMS if it has any. Or maybe something like Elasticsearch might be a better fit. Disclaimer: I've never used Elasticsearch myself.

frankwiles 12y ago

Those sorts of SQL queries aren't very fast compared to a dedicated index/search system like ElasticSearch. Especially when millions of rows are concerned. Think of search engines like ElasticSearch and Solr as being purpose built for "search" rather than ad hoc querying. They offer more advanced searching features like faceting and synonyms, if your example had been "SELECT id FROM pages WHERE title LIKE '%dog'" you could set things up so that matches for 'dog', 'dogs', 'doggie', 'puppy', 'pup', 'canine', and 'mans best friend' all returned the same results.

jobposter1234 12y ago
While you are absolutely correct that this won't scale, and doesn't have a lot of advanced features like faceting and synonyms, it's still a useful technique. I use the SQL LIKE operator all the time, especially when I just need a simple way of searching my data. If you under a million rows, LIKE is reasonably performant.