Re: N-to-N Model: Table Size vs Complexity

[P. White]

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.