4 ms·
The brain-dead perception that "relationships are bad" came from the old brain-dead MySQL query optimizer (which has since been fixed, as far as I know). They d
by zamalek 12y ago
The brain-dead perception that "relationships are bad" came from the old brain-dead MySQL query optimizer (which has since been fixed, as far as I know). They did actually make things universally slower in that database engine and were a universally bad idea if you were using that specific database engine.
Relationships (and by association the normal forms) provide extremely important information/hints to the query optimizer and allows it to do stuff like join elimination[1]. It is my belief that SQL's bad name came from the widespread use of MySQL's partially functional engine and the poor performance caused by the lack of a proper query optimizer.
Aside: sometimes normal forms do cause too much additional overhead during queries, which is when you can use materialized views[2].
Not to mention that a normalized database is ripe for translation into analysis forms (cubes etc.), and that advanced tooling generally has a better understanding of normalized databases.
Basically, there are many more reasons (above the valid ones that you give) to have normalized forms and describe referential integrity.
[1] http://antognini.ch/2010/01/join-elimination/ http://antognini.ch/2010/01/join-elimination/
[2] http://en.wikipedia.org/wiki/Materialized_view http://en.wikipedia.org/wiki/Materialized_view
- imanaccount247 12y ago>came from the old brain-dead MySQL query optimizer (which has since been fixed, as far as I know). It came from the fact that mysql doesn't support joins properly. I do not believe this has been fixed, as far as I know it still only supports nested loop joins.
- zamalek 12y agoYep, choosing the correct joining method is one of the tasks that a query optimizer (a.k.a. join engine) performs. I think someone around here pointed me toward a document that indicated that there was, at the very least, an experimental competent query optimizer in the MySQL codebase. Even then it's a far cry away from "the big guns." Things like statistics, DPVs, etc. simply aren't (edit: or weren't) present in MySQL: which are more examples of a critical components of efficient database design at massive load scales. TLDR; MySQL simply isn't competitive, it's being improved (especially MariaDB) but is a long ways off.
- wvenable 12y agoMySQL's optimizer isn't best of breed but your comment is massively ignorant. MySQL supports transactions, row level locking, nested SQL, and even replication -- how would you ever get the idea that it only ever just supported nested loop joins?
- zamalek 12y agoHe's referring specifically to the features within the joining engine, not MySQL as a whole. [Edit] I'm refuting that this: > MySQL supports transactions, row level locking, nested SQL, and even replication is relevant in the context of what he said.
- wvenable 12y agoMySQL always did more than nested loop joins -- which is literally the worst kind of join. It's the join you get if you have no indexes on anything in any DBMS. I fail to see what your reply here is trying to refute. Even your comment, stating that MySQL doesn't maintain statistics, it's totally wrong and easily checked with 5 seconds of Googling. To your edit: The idea that a DBMS would support so many advanced features and do nested loop joins is laughably ridiculous.
- dsp1234 12y agoI'm no MySQL expert, but the docs for 5.6 specifically state: "MySQL resolves all joins using a nested-loop join method." [0] Can you provide documentation on other join methods? [0] - http://dev.mysql.com/doc/refman/5.6/en/explain-output.html http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
- wvenable 12y agoMySQL sort-merge joins, it's right in that documentation, but it calls everything out as optimizations on nested-loop. http://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-nested-loop-joins/ http://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-n... It doesn't do hash joins which is the biggest limitation but MySQL is pretty good a joins and certainly was never bad enough to warrant it's reputation. It's toolbox is smaller than other RDBMS products but it's not empty.