Skip to content

Instantly share code, notes, and snippets.

@koenbok
Created May 24, 2012 06:20
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save koenbok/2779788 to your computer and use it in GitHub Desktop.
Key Value a la FriendFeed
-- 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment