[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)

[crimsonalucard]

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.