Yeah I think the only way you can use plain, vanilla SQL to query a system is IF its a relational database. Most of the examples are not.. they solve different problems and have vastly different architectures under the hood.
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.
Same with Splunk, it addresses a different domain and solves different problems that cant be easily expressed in SQL.
Same with MongoDB.. its not a relational db. Just because there is a mapping from some SQL queries to some Mongo queries, does not mean they are identical databases.
The query language directly impacts the AST generated, which is necessarily tied very closely to the exact capabilities/internals of a system. This post just feels like its based on a cursory glance of what the systems do.
If all Lucene had was regexes, it would not perform much better than a SQL database throwing regexes at strings. Its precisely because the query language is finer-grained that it can be better optimized for that usecase.
> 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;
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
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.
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.
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.