Re: N-to-N Model: Table Size vs Complexity
Posted by:
P. White
Date: May 14, 2009 08:33AM
Hi.
I'm also working on a project with exactly the same scenario, and currently use UNION to return the results I require (partially because I prefer it, and partially to avoid the scenario where 'A' is a friend of 'B', but 'B' is NOT a friend of 'A').
However, I am very much a newbie in this area, and though everything works, I have no idea on how efficiently it works. Testing with data with a population of about 10 is one thing, using in a live environment with a population of tens of thousands is quite another!
Therefore, may I extend this query, and ask how I should be tuning the initial setup? In my particular case, the friendship links do not change all that frequently, but are mostly read. What storage engine should I be looking at, and how should it be indexed?
at the moment, I have:
CREATE TABLE `db`.`friends` (
`a` char(10) NOT NULL COMMENT 'SELECT a FROM friends WHERE b = person UNION SELECT b FROM friends WHERE a = person',
`b` char(10) NOT NULL,
KEY `friend` (`b`),
KEY `person` USING BTREE (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='mapping relationships between people';
Many thanks.
Phil.