Re:SQL is 43 years old – Here’s why we still use it today
by crimsonalucard on Wednesday May 03, 2017 @12:31PM
(#14257330)
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.
Re:SQL is 43 years old – Here’s why we still use it today
by irishsultan on Wednesday May 03, 2017 @12:46PM
(#14257518)
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.
Re:SQL is 43 years old – Here’s why we still use it today
by crimsonalucard on Wednesday May 03, 2017 @01:28PM
(#14257998)
That tree structure my friend is called a binary search tree. It is essentially a permutation of the same kind of search.
Re:SQL is 43 years old – Here’s why we still use it today
by irishsultan on Wednesday May 03, 2017 @02:39PM
(#14258764)
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)
Re:SQL is 43 years old – Here’s why we still use it today
by crimsonalucard on Friday May 05, 2017 @02:24AM
(#14271492)
When did I say you should index the db as soon as you search? All I'm saying is change the API from a expression based query language to a imperative language.... noSQL.
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.