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
>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
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.
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center sbedberg@stripped |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+