1

When a user registers on my site, they have their own table created in one of my databases. This table stores all of the posts that the user makes. What I would also like to do is also generate them their own MySql user - which ONLY has permission to read, write and delete from their table.

Creating that shouldn't be a problem - I've got Google for that.

What I'm wondering is, let's imagine that I clock myself 10,000,000 or more users at one point in the future, would having that many MySql users affect my database performance?

  • 1
    I would be more worried about a database with 10,000,000 tables than one with 10,000,000 users. This "table per user" idea sounds like bad design, why not just have one table and a user id field. – Ben Robinson Sep 01 '11 at 10:37
  • **YES**, 10,000,000 users make the tables inside mysql.mysql very fat.You should have more budget if you really has 10 millions of users, that's allow you to go for better solution in terms of hardware, database. – ajreal Sep 01 '11 at 10:39
  • User information is stored in one table because that's commonly used. The "table per user" stores information that is only ever used when someone visits their profile page. Is that a bad technique to use then? (I'm new to databases) And my site doesn't have 10,000,000 users, it's only in its first stages of development at the moment - but I'd like it to be capable of supporting a large amount of users from the offset rather than me having to redesign it if the site ever took off. And okay thanks - I'll not use the "user per user" idea. –  Sep 01 '11 at 10:43

2 Answers2

2

For the sake of answering your question, a quick points... before I explain why you are doing it wrong...

The performance hit will come from having massive amount of tables. (The limit is massive so should you ever reach that high, I would for gods sake hope that you recruit someone who can slap your database silly and explain why you have mutilated it so much). Excuse the harshness :)

Okay, now onto how you should actually be doing it.

Multi-Tenancy

First, you need to learn about how to design a database that is designed for multi tenant application. This is exactly what you are creating by the sounds of it, but you are doing it COMPLETELY wrong. I cannot stress that enough.

Here are some resources which you should read immediately.

Then read this question: - How to design a multi tenant mysql database

After you have done that. You should learn about ACL (Access Control Lists).

If you explain what sort of data you are trying to model, I will be happy to update this post with a simple table schema to match what you might require.

Community
  • 1
  • 1
Layke
  • 51,422
  • 11
  • 85
  • 111
  • I'm 17, I can't really afford to recruit anyone ;) I didn't realise that tables would cause such a performance drop though - I'm thankful that you've pointed this out for me. I've not actually started designing the database yet - but I'll read through your provided links so that I know how to do it correctly, thank you. –  Sep 01 '11 at 10:48
  • 1
    You don't recruit anyone.. I was just saying that if you get up to 10000000 users, the chances are you have just hit a gold mine, in which case you would definately be able to hire someone to fix the problems :) – Layke Sep 01 '11 at 10:58
  • Ah yes, we were both thinking at different ends of the timeline then :) If the site ever took off, you can be reassured that I'd hire someone experienced in this field! It looks like I've got a lot to learn about MySql before I should really attempt this - so I'll be finding myself some book and good articles. –  Sep 01 '11 at 11:55
1

Yes it will drop your performance. Usually a server application uses a database connection pool with several connections (say app_user is connected 5 times). Every SQL request is handled by one of these connections. That way the overhead of creating a new connection, handling the query and dropping the connection is reduced to a minimum.

Now in your scenario every user would have his own table with its own user. That means if a user logs into your application he has to open his own connection, as he has to use his specific user account. Now instead of just 5 connections 10,000 connections have to be opened. That would not scale as each connection has its own thread and uses some ram space. Furthermore there are only about 64k ports available for your connections.

So your application would not scale for that many users.

ayckoster
  • 6,707
  • 6
  • 32
  • 45