0

I am quickly putting together a buddy / friends list where a user will have a list of buddies. I will be be using a relation database for this and found the following post:

Buddy List: Relational Database Table Design

So the buddy table might look something like this:

buddy_id     username
1            George
2            Henry
3            Jody
4            Cara

And the table for user's buddy lists would look something like this:

user_id      buddy_id
2            4
1            4
1            3

My question is how fast would it be if a user had 20,000+ buddies and wanted to pull there entire list in under a second or so. I would be running this on a pretty typical MySql setup. Would there be any key optimizations or db configurations to get this fast?

Community
  • 1
  • 1
Luke Belbina
  • 5,708
  • 12
  • 52
  • 75

1 Answers1

0

What does "pull their entire list" mean to you?

I can select 20,000 rows from a large "buddy" table (few million rows) in 15 milliseconds on my computer, but that doesn't include network transit time (both directions), formatting, and displaying on a web page. (Which I presume is the point--a web application.)

You'll need an index that covers user_id, but creating a primary key on (user_id, buddy_id) should do that.

Scripting languages are useful for generating test data. I'm using ruby today.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • pulling list means I could query for user_id 1 lets say and it would return Jody, Cara so it does a join (doesn't only return the buddy_id) – Luke Belbina Nov 29 '11 at 16:56