undefined | Better HN

0 pointscrimsonalucard9y ago0 comments

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?

0 comments

3 comments · 1 top-level

irishsultan9y ago· 2 in thread

> 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.

crimsonalucardOP9y ago

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.

irishsultan9y ago

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.

1 more reply

j / k navigate · click thread line to collapse