[deleted]
High scalability: SQL and computational complexity
11–20 of 21 posts
Re: High scalability: SQL and computational complexity
#12If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, but basically you run the query wrapped in a command that says "fix the execution plan of this query." The execution plan gets saved in some table keyed by the query text. Whenever Oracle sees that query, it commits itself to the same execution plan. This feature is like a decade old.
A lot of the NoSQL arguments are really "No-MySQL" or "NoMoney" arguments. If you've never used Oracle (or DB2, or even SQL Server), you really don't have a full understanding of the capabilities and limitations of SQL databases. AFAICT, there's almost nothing in MySQL that wasn't old and boring in Oracle over a decade ago.
Re: High scalability: SQL and computational complexity
#13If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, but basically you run the query wrapped in a command that says "fix the execution plan of this query." The execution plan gets saved in some table keyed by the query text. Whenever Oracle sees that query, it commits itself to the same execution plan. This featu…
Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?
Re: High scalability: SQL and computational complexity
#14If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, but basically you run the query wrapped in a command that says "fix the execution plan of this query." The execution plan gets saved in some table keyed by the query text. Whenever Oracle sees that query, it commits itself to the same execution plan. This featu…
Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?
Well, USE INDEX is just a hint, and MySQL is free to ignore it. You probably mean FORCE INDEX, but I've still encountered situations where the index was ignored because the optimizer (incorrectly) thought there was no way to use it. (That was probably a bug, though)
Re: High scalability: SQL and computational complexity
#15"Oh, and by the way, RethinkDB is hiring right now to create the next generation of products that will fix all these problems with SQL."
It's an interesting article. But isn't he just saying, P=NP is a problem, so let's ban P=NP? Is this programmer newspeak?
Re: High scalability: SQL and computational complexity
#16If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, but basically you run the query wrapped in a command that says "fix the execution plan of this query." The execution plan gets saved in some table keyed by the query text. Whenever Oracle sees that query, it commits itself to the same execution plan. This featu…
Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?
The Oracle fixed execution plan feature fixes the entire execution plan, not just which indexes are used. Think about a query where there's ten (indexed) tables involved. Saying "use this index for this table, this index for that table, etc." isn't the same as saying "First, filter the third table down to the fields that match the criteria given for that table using index Foo, then filter the 7th table given its criteria using index Bar, then join them together using nested loops, then join them to the result of ...".
Think about an Oracle execution plain as your declarative SQL query rewritten as a procedural program as you would write it in C/Java/Python/Ruby with hashtables, nested loops, if statements, etc.
Re: High scalability: SQL and computational complexity
#17Earlier quoted context omitted.
Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?
Well, USE INDEX is just a hint, and MySQL is free to ignore it. You probably mean FORCE INDEX, but I've still encountered situations where the index was ignored because the optimizer (incorrectly) thought there was no way to use it. (That was probably a bug, though)
[deleted]
Re: High scalability: SQL and computational complexity
#18"Oh, and by the way, RethinkDB is hiring right now to create the next generation of products that will fix all these problems with SQL." It's an interesting article. But isn't he just saying, P=NP is a problem, so let's ban P=NP ? Is this programmer newspeak?
No. What I'm saying is that P=NP is a problem, so don't put O(2^N) algorithms into your production software. People seem to be offended by the fact that you can't run certain queries in realtime the moment you start talking about scale. It isn't my arbitrary definition, it's a fundamental limit in the physical universe.
Re: High scalability: SQL and computational complexity
#19Earlier quoted context omitted.
You don't allow N O(1) queries, you allow k O(1) queries, where k is a small constant independent from N. It is simply a physical reality that a massively scalable realtime system cannot run anything other than a small number of log(N) queries. You can't run N O(1) queries or one O(N) query - there is just no way to evaluate it in real time. If you need to present the result of such a query to users, you can run it a…
But the users are going to issue those N queries while you're still at the whiteboard talking about big-O notation. Exposing a full query language to the database system means that you have the opportunity to optimize the query fully. Limiting the query language only limits your ability to optimize, so you won't even be able to explain to the user that there's an alternative way to get their answer without bringing d…
Limiting the query language only limits your ability to optimize
This seems backwards. The more general a language is, the harder it is to analyze and transform, and that is what optimizers do.
Re: High scalability: SQL and computational complexity
#20"Oh, and by the way, RethinkDB is hiring right now to create the next generation of products that will fix all these problems with SQL." It's an interesting article. But isn't he just saying, P=NP is a problem, so let's ban P=NP ? Is this programmer newspeak?
No. What I'm saying is that P=NP is a problem, so don't put O(2^N) algorithms into your production software. People seem to be offended by the fact that you can't run certain queries in realtime the moment you start talking about scale. It isn't my arbitrary definition, it's a fundamental limit in the physical universe.
I get it. I just wanted to point out that's what we're talking about, because it's also still a P=NP problem to even know if something is a P=NP problem, so all we can do, it would seem, is put up guards against the likely suspects. And that's all great. I'm trying not to be a negative nancy, but its statements like these that just seem so sloppy:
"If this holds true, even if we can trivially parallelize each query, an exponential function (the amount of information) divided by a polynomial (number of machines) still dominates the logarithmic function we defined earlier as acceptable. This means that given modern trends, if a given query isn’t scalable vertically, it also isn’t scalable horizontally, which makes SQL fundamentally unscalable, period."
Cut out the fluff and all that's saying is "Given modern trends, SQL is fundamentally unscalable, period." And why? Because anyone can write a bad query. And what's the answer? Complexity guards? Well, it certainly sounds good, more guards in SQL, or let's not call it SQL if there are organizational issues (i.e. non-modular design) and syntactical paradigms to improve. And lets insert some other [undescribed] computational theory to improve it, but the paradigms don't change the algorithmic reality, they can only hope to strategize better.
Having said all that, I don't want to be misunderstood, it sounds like there may be some very interesting ideas behind all of this. It's hard to know in a case like this whether to comment on something, considering that much of the comment is negative. What motivates me is that in bullet points the article seems to imply something quite different than it sounds like when taken at face value. And I honestly appreciate push back if I'm just wrong about that. It's all about filters required to find the good stuff in all the the hyperbole that surrounds us.