3 ms·

> Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed usin

> Lucene for example doesn't use SQL because its really solving a different problem - text search. Its a language dedicated to what could only be expressed using something like `LIKE` and regexes in SQL. There are much better options than LIKE and regexes in SQL for text search. To mention but two: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; https://www.postgresql.org/docs/current/static/textsearch-intro.html https://www.postgresql.org/docs/current/static/textsearch-in... SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-lan... All main engines support full text search in one way or another. TSQL, Oracle, DB2... even SQLite. What more, these engines all support inverted tree indexes in one form or another - i.e. the same type of index Lucene uses - to make this efficient. This is completely different from the typical LIKE or regex query against a column with a BTree index.

Xylakant 8y ago
If you truly believe that MySQLs MATCH comes close to be plethora of different queries you can fire at an ES or Solr server, you’re missing some fundamental part of the picture. Sure, it might have been possible to squeeze Lucenes or elasticsearchs query language into something that looks like SQL, but it would still remain a custom SQL dialect which would require substantial domain knowledge to handle. The fundamental problem in that domain is not the query language, it’s knowing which query on which underlying field definition will produce the desired result. Squeezing the query language into some sql-like shape will provide marginal gain at best.
- ddebernardy 8y ago
  I can't speak for MySQL, as I haven't used it in a while, but I'd pick a properly configured Postgres full text over ES in a heart beat, if only because it means one less dependency (tree) to worry about. (As a bonus, the beauty of Postgres allows to use ES as an index type if you so wish [0].) Also: > Squeezing the query language into some sql-like shape will provide marginal gain at best. ... is disingenuous at best. In both examples I raised (and in the ones you'll find if you query the others) the engines have functionality to accept user input pretty much as is - meaning as your typical mom would type it in a search field. If you need extra criteria from there and the dialect's full text syntactic features, you can use regular SQL conditions (and joins, and aggregates, etc.) on the subset of records found. The most discriminating criteria/index will be the one related to the search query in most cases. And when not, lucky you - your query planner did not hammer everything with the same sledgehammer, thus demonstrating why you should be using SQL. [0]: https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
  - smsm42 8y ago
    I'm not sure what "properly configured" means but ES has huge array of options for search which something simple like MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) can't even begin to coming close to cover. Look at their docs, there are literally hundreds of options, dozens of search modes, dozens of analyzers and filters, scoring configurations, and so on and so forth. I am not familiar specifically with Postgres but I highly doubt Postgres supports all that or even a serious part of that - at least natively. What I see at your zombodb link is just a way to make Postgres proxy requests to ES - complete with ES syntax and/or Query DSL. The only difference is that instead of connecting to ES directly you connect to Postgres and then add "SELECT" thing in front of your ES Query DSL. If it makes something easier, fine, but it's not "Postgres full text over ES".
TheCoelacanth 8y ago
What you really mean is that all of the major DB engines created their own query language for dealing with full-text search. None of those things are SQL, they are extensions to SQL.
- ddebernardy 8y ago
  If memory serves me well, ANSI SQL (the W3C of SQL) recommends the syntax adopted by TSQL and DB2. So technically, some flavors of this are not extensions to SQL, but indeed part of it. Think of it as browser prefixes. Sooner or later all engines agree on a common syntax, with each one needing to support some legacy code. And in contrast with what happens when you use some third party app, you can further filter matched rows, join them with other tables to your heart's contents, aggregate them, apply window functions, and what have you.

Xylakant 8y ago

If you truly believe that MySQLs MATCH comes close to be plethora of different queries you can fire at an ES or Solr server, you’re missing some fundamental part of the picture. Sure, it might have been possible to squeeze Lucenes or elasticsearchs query language into something that looks like SQL, but it would still remain a custom SQL dialect which would require substantial domain knowledge to handle. The fundamental problem in that domain is not the query language, it’s knowing which query on which underlying field definition will produce the desired result. Squeezing the query language into some sql-like shape will provide marginal gain at best.

ddebernardy 8y ago
I can't speak for MySQL, as I haven't used it in a while, but I'd pick a properly configured Postgres full text over ES in a heart beat, if only because it means one less dependency (tree) to worry about. (As a bonus, the beauty of Postgres allows to use ES as an index type if you so wish [0].) Also: > Squeezing the query language into some sql-like shape will provide marginal gain at best. ... is disingenuous at best. In both examples I raised (and in the ones you'll find if you query the others) the engines have functionality to accept user input pretty much as is - meaning as your typical mom would type it in a search field. If you need extra criteria from there and the dialect's full text syntactic features, you can use regular SQL conditions (and joins, and aggregates, etc.) on the subset of records found. The most discriminating criteria/index will be the one related to the search query in most cases. And when not, lucky you - your query planner did not hammer everything with the same sledgehammer, thus demonstrating why you should be using SQL. [0]: https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
- smsm42 8y ago
  I'm not sure what "properly configured" means but ES has huge array of options for search which something simple like MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) can't even begin to coming close to cover. Look at their docs, there are literally hundreds of options, dozens of search modes, dozens of analyzers and filters, scoring configurations, and so on and so forth. I am not familiar specifically with Postgres but I highly doubt Postgres supports all that or even a serious part of that - at least natively. What I see at your zombodb link is just a way to make Postgres proxy requests to ES - complete with ES syntax and/or Query DSL. The only difference is that instead of connecting to ES directly you connect to Postgres and then add "SELECT" thing in front of your ES Query DSL. If it makes something easier, fine, but it's not "Postgres full text over ES".

TheCoelacanth 8y ago

What you really mean is that all of the major DB engines created their own query language for dealing with full-text search. None of those things are SQL, they are extensions to SQL.

ddebernardy 8y ago
If memory serves me well, ANSI SQL (the W3C of SQL) recommends the syntax adopted by TSQL and DB2. So technically, some flavors of this are not extensions to SQL, but indeed part of it. Think of it as browser prefixes. Sooner or later all engines agree on a common syntax, with each one needing to support some legacy code. And in contrast with what happens when you use some third party app, you can further filter matched rows, join them with other tables to your heart's contents, aggregate them, apply window functions, and what have you.