At 8:37 AM +0200 7/12/07, Olav Mørkrid wrote:
say you want to keep track of who knows whose name at a party, storing
one table row per instance explodes into n*(n-1) rows (a million rows
for thousand people).
a) can mysql cope with this, and it's more a question of storage and
processing power?
or b) does such a table spell certain doom for a database? if so, how
does one solve this problem efficiently?
Well, one would assume not everyone knows everyone else. So you have
a People table (1,000 records in your example) and a Friends table
that looks something like
PersonId
FriendId
both of which are foreign keys pointing to the People table (which
would normally have an autoincremented primary key). Perhaps you
could include a 'quality of friendship' column as well. Even if you
had a party of 1,000 people where everyone knew everyone, a table of
1 million records is pretty reasonable. It all depends on your query
& index design (make friends with the EXPLAIN command). If you go
through the mailing list archives, you'll find numerous people with
multiple tables with billions of records.
steve
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center [EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
