[remotehack]

SQL is about relational theory; all that matters is the data.

> Instead wouldn't it be better for the language to explicitly allow the user to apply algorithms or procedures to make things more efficient

That...is hacking.

[crimsonalucard]

No it is not hacking.

By explicit I mean BinarySearch(Table, x = name) rather than "SELECT * FROM Table WHERE x = name"

Let me explain to you why "explicit" is better... Why should "SELECT column_name1, column_name2 FROM table" be more efficient than "SELECT * FROM table"? The abstraction is so leaky that in order to make a query better you resort to a language hack that only makes sense when you understand the instructions SQL compiles down into. This is bad. Leaky abstractions are bad. I shouldn't have to know what the SQL query is doing to optimize....

In web development your application servers use languages like go or python that are closer to the metal which allow us to explicitly deploy certain algorithms without this strange layer of SQL expressions that compiles to imperative code. This leads to faster applications that are easier to optimize at the expense of using terse highly abstract expressions such as those found in SQL.

Here's the strange part of web development. Everyone knows that the bottleneck for most websites are in the database. Yet why do we deploy easily optimizable imperative languages in the application server while putting a highly inefficient SQL expression language over the main bottleneck (the database)?

Shouldn't it be the other way around? Shouldn't we have Web application servers written in highly abstract functional languages while Database languages written in easily optimizable imperative code that is closer to the metal?

[irishsultan]

> By explicit I mean BinarySearch(Table, x = name) rather than "SELECT * FROM Table WHERE x = name"

So are you saying you want to force your DB to store rows in a particular order? Because doing a binary search on unsorted data isn't going to work. But forcing your data to be sorted in a particular way is either going to require you to insert with explicit instructions to do it in the right place, or your DB to know that it's sorted on that attribute, in which case you shouldn't really need to tell that it should do a binary search, because it's obvious.

[crimsonalucard]

Dbs do that ANYWAY when you index. In this case assume both tables are indexed by x.

It's obvious for a single SQL expression because you've studied what happens underneath the abstraction, but when expressions get complex it's hard to reason about what a sql expression is doing.

[irishsultan]

No, what DBs do when you index is have a separate data structure from the main table, this doesn't affect what the table itself looks like. Nor is it likely this index will support a binary search (because a binary search works on arrays, but an index is most likely a tree structure, even when it's not it's unlikely to be an array).

What you presumably want is a way to specify which indices to use and perhaps to specify an order on the joins and perhaps the type of join as well.

[crimsonalucard]

That tree structure my friend is called a binary search tree. It is essentially a permutation of the same kind of search.

[irishsultan]

Sure, it's clearly sort of the same thing, but it's not in fact a binary search (if only because few DBs will have a tree with a branching factor of 2), and this is assuming your index is in fact a tree and not a hash index (do you really want to specify what kind of index it is, each time you use it? for that matter do you really want to prevent usage of an index just because you or one of your colleagues wasn't aware of it's existence)