Schema-Free MySQL vs NoSQL

<p><pre><code> Instead of defining columns on a table, each attribute has its own table (new tables are created on the fly), which means that we can add and remove attributes at will. In turn, performing a select simply means joining all of the tables on that individual key. </code></pre> ... and now you know why nobody is doing this.<p>Back before NoSQL had a name (and fast implementations) and we would just build key-value stores inside of relational databases and describe them to people as "tall, skinny tables", this was how you would construct a query.<p>Of course, it was a lot easier in a skinny table than the method you describe because you'd simply join the table back on itself n times rather than having n tables sitting around in your database.<p>Regardless, doing things that way was generally regarded as a bad idea even back in the 90's, and was only something to consider when you had highly-configurable things to describe that didn't fit well into a relational structure. Everybody realized that you suffered huge performance hits at query-time, and you were essentially eliminating the possibility of reporting.<p>So no, given that NoSQL is all about using key/value stores to boost performance by trading off reportability, this solution (which actually trades both sides of that equation away) is not a replacement.

Interesting article.<p>I've recently been doing some load-testing of key-value stores for caching, and also found that MySQL compares pretty favourably with some of the new kids on the block.<p>Just talking a "create table (key char(64) not null, value blob not null, index (key))" schema. (Plus some MySQL performance tweaks, which I won't go into here)<p>Some advantages:<p>* When it comes to redundancy and failover, you can re-use the existing master/slave replication functionality in MySQL, rather than worry about whether your k/v store of choice supports anything like this, how stable it is and how to configure and manage it etc. One less thing to worry about especially if you're already doing mysql replication for other data.<p>* MySQL can keep hot data in RAM and cold data on disk, and is pretty tuneable in this regard; some of the trendy k/v stores aren't so hot on this front (eg Redis, although they're addressing it in version 2)<p>* With InnoDB tables (which are pretty good for k/v functionality) you get the transaction support which can save your ass from some nasty race conditions with concurrent processes writing back to a cache. Especially handy if your main relational dataset is also in MySQL since you can update the normalised data and the cached data in one transaction<p>* You can join 'relational-style' tables directly to the 'k/v-style' tables, rather than having to fetch a bunch of IDs and then look them up (for some stores, one at a time) in a separate store. Which is a great when you have some infrequently-changed data that can be denormalised as cached blobs, and some frequently-changing data which refers to it.<p>* Possibly more...

The OP's suggestions are pretty bad, as jasonkester points out. But for a specific class of problems (ones that are both performance or JOIN intensive and require the awesome indexing/sorting capabilities in SQL solutions), schema-less MySQL, <i>when done "correctly"</i> is awesome. I designed an application that needed to do graph traversal in real-time, so normalized SQL wasn't an option for me. I tried various NoSQL solutions, but they all either lacked important features or ran insanely slow. I ended up settling with a design similar to how FriendFeed uses MySQL: <a href="http://bret.appspot.com/entry/how-friendfeed-uses-mysql" rel="nofollow">http://bret.appspot.com/entry/how-friendfeed-uses-mysql</a><p>Not only was the solution faster, but it made code so much more concise, because you can just serialize Python objects to the database rather than write a huge database abstraction layer. For me this is even more valuable than the performance ramifications.<p>If you're using Python+MySQL, be sure to check out Tornado's database wrapper (<a href="http://github.com/facebook/tornado/blob/master/tornado/database.py" rel="nofollow">http://github.com/facebook/tornado/blob/master/tornado/datab...</a>) because it handles all sorts of sharp corners that straight up MySQLdb won't (like working with binary data, reconnecting when necessary, etc.)

If you want to look at a paper on performance of such a system check out <a href="http://cs-www.cs.yale.edu/homes/dna/papers/abadi-sigmod08.pdf" rel="nofollow">http://cs-www.cs.yale.edu/homes/dna/papers/abadi-sigmod08.pd...</a>