One to Many Relation Design Performance

Question

When we create a database design that involves 1-to-many relationships, there is a potential risk of performance loss as the data in the relationship grows.

For example, lets take a simple 1 to Many relationship involving two tables.

[User] 1 ----- m [Friends]

A user can have many friends. A common design for this would be two tables one of which contains all the users, and another that contains all the friends of that user with a user id as the foreign key in Friends.

But technically, as the number of users grow, and subsequently the number of friends grow, then there will be performance implications retrieving a list of the users friends.

Is there a design pattern to solve this kind of problem, or at this stage we have to rely on computing power to maintain performance?

Answer 1

Test your queries before you assume performance will be bad.

Generate test data on your test environments, run your queries, check the result, tune your queries, adjust indexes, check weather or not performance improved, repeat until satisfied.

Answer 2

But technically, as the number of users grow, and subsequently the number of friends grow, then there will be performance implications retrieving a list of the users friends.

Yes. so wwhat?

Use indices. Buy hardware. In exactls this order (seen more than one overlaoded server because the the programmer never read http://use-the-index-luke.com/).

Your question is a non issue because brutally speaking there is no way around readling with more data when you have more data. Which is why certain databaes require more than a 5 USD per month cheap low end virtual machine and it is not uneard of to have a terabyte of memory these days in a database server for caching.

Basically you say "I open a shop, i keep inventory. Now when I keep more inventory, I need more space, and I can not really handle the work alone before breackfast anymore, how can I solve this" And the answer is - get more space and hire ppl. THe answer in sql is - get a larger server.

That is unless you did something not smart like not putting the proper indices there, and THAT IS IT.

A lower end server (8 cores, dual opteron with 4 cores each, about 5 years old) in a virtual machine with 6 cores and 48gb memory is what I use at a customer to aggregate rows with hundreds of millions of entries in the seleted result (out of a table otf arond 10 billion rows and growing) and yes, weh ave a carefull layed out disc subsystem (NEED IO), are a little short on RAM and sometimes max out the CPU.

But there is nothing I can do against it.

With more data you need more powerfull hardware.

WIth indices, performnce gets word roughly (depends on a lot of factor) on LOG(n) - so it does not sloq linearly. If you skip indices it is linear - 2x as long table, 2x as long query and living in pain. So be competent (altough indices at least inthe baseline form are very basic) and then throw hardware at the problem.

No other solution.

Answer 3

You can use indexing features while retrieving friends list on the basis of user selection from friends table.