TE
科技回声
首页24小时热榜最新最佳问答展示工作
GitHubTwitter
首页

科技回声

基于 Next.js 构建的科技新闻平台,提供全球科技新闻和讨论内容。

GitHubTwitter

首页

首页最新最佳问答展示工作

资源链接

HackerNews API原版 HackerNewsNext.js

© 2025 科技回声. 版权所有。

SQL AntiPatterns Presentation from MySQLConf 2009

44 点作者 jwinter将近 16 年前

3 条评论

gcv将近 16 年前
Fascinating. I haven't finished reading the entire presentation yet, but I already stumbled on something.<p>The author lists a drawback of the first antipattern ("metadata tribbles"): a table split into multiples to keep its size down requires tricky querying, specifically, it requires a union across all splits (slide 10). It also requires extra effort to keep table structures synchronized (slide 11). Then, in a solution to his second antipattern ("entity-attribute-value"), he suggests doing something quite similar to his first antipattern: creating multiple tables with nearly identical columns and using a union to query across them (slides 27 and 28).<p>Hello? So to resolve antipattern 2 you just apply antipattern 1? This is why precisely why relational databases for most problems look like square pegs being hammered into round holes.<p>[EDIT] Not sure if it's worth finishing reading this presentation. Slide 56, a "solution" to the problem of storing a hierarchy in a relational database. Breadcrumbs. Not bad, except that the query now requires a "like" clause. Whoops, can't use an index on that column anymore! (Feel free to correct me if there exists a RDBMS which can use an index on a "like.") Hope the author doesn't mind a nice table scan on that query. He doesn't even mention this problem.
评论 #697805 未加载
评论 #698067 未加载
评论 #698059 未加载
评论 #697804 未加载
评论 #698054 未加载
snorkel将近 16 年前
I disagree with slides 20 - 30 (calling "entity-attribute-value" tables an antipattern). Variable attributes table not only gives you a lot of application flexibility but also helps performance in that you're not polluting your primary table with extraneous metadata columns that are hardly ever referenced. It doesn't surprise me that DB programmer would look at that and be offended that it's not purely referential -- not everything that happens in a database has to be referential to everything else. Sometimes databases are just dumb data stores. Not all problems are solved by "referential integrity"
评论 #698498 未加载
gchpaco将近 16 年前
I was expecting something more MySQL specific, and was pleasantly surprised. It's certainly worth thinking twice before violating any of these.