SEO

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?

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 sbedberg@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+

steve

i'm happy to hear your optimism, handling billions of rows sounds
amazing. but i'd like to be fully assured.

a frequent use of the table will perform selects that show:

a) people you have seen
b) people you haven't seen yet

an average user will quite quickly build a list of thousands of people
he has seen, so you will get selects like:

select * from user where id not in ([list of seen users] ) and [other criteria]

how many users -- and simultaneous users -- can a normal mysql server
handle just fine when you've got people doing selects like this quite
often if not "all the time"?

On 12/07/07, Steve Edberg <sbedberg@ucdavis.edu> wrote:

> 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.

At 03:31 AM 7/12/2007, Olav Mørkrid wrote:
>steve
>
>i'm happy to hear your optimism, handling billions of rows sounds
>amazing. but i'd like to be fully assured.
>
>a frequent use of the table will perform selects that show:
>
>a) people you have seen
>b) people you haven't seen yet
>
>an average user will quite quickly build a list of thousands of people
>he has seen, so you will get selects like:
>
>select * from user where id not in ([list of seen users] ) and [other
>criteria]
>
>how many users -- and simultaneous users -- can a normal mysql server
>handle just fine when you've got people doing selects like this quite
>often if not "all the time"?

You really haven't given a complete SQL structure so the answer is as
vague, like between 1 and 1000 queries/second. Friendstr, Flickr, and
Wikipedia all use MySQL. You need to throw a lot of RAM at the problem and
perhaps distribute the load with a cluster database if the site gets really
busy.

There was an excellent article in Wikipedia describing how they built it
using MySQL but unfortunately I can't find it.
Wikipedia also had this list of MySQL winning site:

Mike


MySQL Application of the Year winners

2007
* <http://en.wikipedia.org/wiki/YouTube>YouTube
* <http://en.wikipedia.org/wiki/Amp%27d_Mobile>Amp'd Mobile
* <http://en.wikipedia.org/wiki/Adobe>Adobe

2006
* <http://en.wikipedia.org/wiki/Nokia>Nokia, using MySQL Cluster to
maintain real-time information about mobile network users.
* <http://en.wikipedia.org/wiki/Flickr>flickr, Using MySQL to manage
millions of photos andusers.<http://en.wikipedia.org/wiki/MySQL#_note-23>[25]
* <http://www.netqos.com/>NetQOS, embeds MySQL to manage the world's
largest networks including Chevron, American Express and Boeing.

2005
* <http://en.wikipedia.org/wiki/CNET>CNET Networks
* <http://en.wikipedia.org/wiki/Friendster>Friendster, more than 85
million dynamic page views per day, able to support more than 1.5 billion
MySQL queries per day
* <http://en.wikipedia.org/wiki/Wikipedia>Wikipedia, more than 200
million queries and 1.2 million updates per day with peak loads of 11,000
queries per second



>On 12/07/07, Steve Edberg <sbedberg@ucdavis.edu> wrote:
>
>>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.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm