Key Value a la FriendFeed

friendfeed.sql

-- http://backchannel.org/blog/friendfeed-schemaless-mysql

DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS person_index_name;
DROP TABLE IF EXISTS person_index_age;

CREATE TABLE person (
	key CHAR(32) NOT NULL, 
	val TEXT,
	PRIMARY KEY (key)
);

CREATE TABLE person_index_name (
	key CHAR(32) NOT NULL, 
	val VARCHAR(255),
	PRIMARY KEY (key)
);

CREATE TABLE person_index_age (
	key CHAR(32) NOT NULL, 
	val INT,
	PRIMARY KEY (key)
);

CREATE INDEX person_index_name_index ON person_index_name (val);
CREATE INDEX person_index_age_index ON person_index_age (val);

INSERT OR REPLACE INTO person (key, val) VALUES (
	'6975b0d926f2495cba824d38c0d1aa79',
	'{"name": "Koen Bok", "age": 29}'
);

INSERT OR REPLACE INTO person_index_name (key, val) VALUES (
	'6975b0d926f2495cba824d38c0d1aa79',
	'Koen Bok'
);

INSERT OR REPLACE INTO person_index_age (key, val) VALUES (
	'6975b0d926f2495cba824d38c0d1aa79',
	29
);

SELECT * FROM person WHERE key IN
	(SELECT key FROM person_index_age WHERE val > 10);

SELECT * FROM person WHERE key IN
	(SELECT key FROM person_index_age WHERE val < 10);