Can someone explain how do we implement simple key/value stores with mysql?

http://royal.pingdom.com/2010/06/18/the-software-behind-facebook/:

Facebook uses MySQL, but primarily as a key-value persistent storage, moving joins and logic onto the web servers since optimizations are easier to perform there (on the “other side” of the Memcached layer).

Can someone explain how do we implement simple key/value stores with mysql? Is it simply a table with bigint as primary key + a single column of LONGTEX开发者_运维技巧T ?

The starting point should really be "is your data relational?" If so, use a relational db!

Key-value is a great solution for non-relational data, but if your data is relational, use SQL and be done with it.

To answer your first question, yes, a key/value store is just that, you store a key and a value associated with that key. And you query based on the key.

The big advantages you get from this is,

• Scalability. You can now easily distribute your data across many(thousands) machines. This is something traditional RDBMS is not good at, joins and acid guarantees across many machines is either impossible or very very slow.

Facebook also have a lot of data that doesn't fit the relation model that ordinary RDBMS uses, namely graphs. That means they query/store/handle the graph nature of the data themselves instead of handling with SQL.

The cost of doing it that way is complexity, and often you have to give up a few points of ACID properties.

The rest of us, that's not facebook/google/linkedin/etc. that only need to handle sites with up to just a few million users can usually just stick to using a traditional database.