3 ms·
Eliminating data redundancy isn't about saving space and can increase space requirements depending on the data. It's really just the database equivalent of the
by wvenable 12y ago
Eliminating data redundancy isn't about saving space and can increase space requirements depending on the data. It's really just the database equivalent of the DRY[1] principle. Which is itself about maintainability, intelligibility, and real application behaviors. Although I will admit that 4th and 5th forms can trade intelligibility for flexibility.
[1] http://en.wikipedia.org/wiki/Don%27t_repeat_yourself http://en.wikipedia.org/wiki/Don%27t_repeat_yourself
- zamalek 12y agoThe 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