3 ms·
> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age. > SELECT * FROM users WHERE name = 'jorge'
by cwyers 11y ago
> In this query, we get all the users with the name 'jorge' are queried and then ordered in descending order by age.
> SELECT * FROM users WHERE name = 'jorge' ORDER BY age;
> If we wanted to dig deeper into this query, we might want to know if the "WHERE" is getting executed before the "ORDER BY". Can we tell from the query if this is the case? No, we can't. You'd have to look it up.
No I wouldn't. I can look right at that query and tell you what order those two things occur in. The order makes sense -- of course you don't sort the results before you filter the results, the SQL database is not a moron. In fact, in this weird "explicit" query language, you have to remember the SAME ORDER -- put the WHERE in front of the ORDER BY (or the filter in front of the orderBy, in RethinkQL logic). Except if you forget the order, you can end up creating a query that performs many times worse than it has to. Whereas in SQL, even if you forget the most basic information about the query plan possible, the query planner will choose a pretty good execution strategy for your query.
And if you can't figure out how that simple SQL query is going to be executed by the server just by reading it, why on Earth do you need a query language that does not just allow but requires you to to set your own query plan? The ability to shoot yourself in the foot isn't a feature.
- takeda 11y agoAlso important thing to note is that optimal query plan can be different based on the data you have. To get a good performance you will chose a different strategy when your query has 3 jorges out 1M records vs if you have 900k jorges in 1M records or when the table only contains 100 entries. Why indexes are not always a good idea? To use an index you need to make at least two lookups per entry, one for index and one to fetch the data. If your query will fetch 900k rows out of 1m rows table, much faster is just to read the data and just filter out values we are not interested in.
- hobs 11y agoIn terms of order, it's not clear that the order by happens "after" the where clause, for instance, what if the results are pre-ordered? If the index used guarantees an order, and that order is the same as your order by, it is redundant and no ordering will need to occur. However, I agree with the thrust of your statement, and if you cant figure out SQL's query plans, writing your own from scratch may be a tall order.
- ngzhian 11y agoThe SQL version reads left-to-right, similar to how ReQL's version reads top to bottom, and both reflects the execution order (in terms of a common way of text flow). From my understanding, ReQL does not do any form of optimization based on the complete query, the `.()` punctuates the flow of command. Whereas and SQL statement is just one compound thing made up of different commands where the SQL engine can optimize, which makes it hard to tell.
- pacala 11y agoEvery so often the optimizer does it wrong. Instead of reading query plans [new language] and adding a layer of hints [yet another language], how about having a language that allows you to specify the precise order when you eventually care, and optimizes anyway by default. As opposed to three languages layered over each other in a fairly ad-hoc way. val q = Q.from(table).select(foo).filter(foo > 3) val q = Q.from(table).select(foo).filter(foo > 3).verbatim() PS. Also, the query plan language is now the same as the query specification language.
- kragen 11y agoThis is a great idea. It's usually known as the relational algebra.
- friendzis 11y agoAbility to shoot yourself in the foot IS a feature (or inevitability, depends on how you look at it). Requirement to shoot between feet is not, though. The problem here is that SQL is declarative and there is nowhere to dig deeper - data set is described. That's what SQL does - describes data sets. And any attempt to dig deeper will lead to imperative code. With SQL I would actually hope that I cannot (statically) look up execution order without taking indexes, partitions and what-not into account.