Mysql: limits on table count, rows, and such

Question

I'm working on a social network.

From what I've gathered, there's a limit at 1792 for number of tables in a mysql cluster. I will probably end up with 100-200 tables for now. Is this reasonable/acceptable? Or is my db schema horrible like this? What is reasonable for a large scale project such as a social network?

Also, what then is the limit to number of rows per table? Some of my tables will be limited to a few dozen or a few hundred rows, but others (such as user messages and statistics) can get into the millions, billions. Is there a reasonable limit to how many rows I should place into each table? If it's better for me, I can create statistics tables by year (stats2010, stats2011).

I can read the internet all day, but most of the information out there only tells me what are min/maxes for database design, and not what is good db design. The earlier in my project's life I can solve these problems the better I will be in the long run.

Any other crucial pointers I should know about? I am still learning about max connections, upload timeouts, and dozens more, but I would like to start with this. Thanks for any help you can give.

Answer 1

...will probably end up with 100-200 tables for now. Is this reasonable/acceptable? Or is my db schema horrible like this? What is reasonable for a large scale project such as a social network?

It's hard to say without any details about what you are doing in the tables. "social network" isn't enough to me for context, but I see it having tables for:

• Users(user_id, details, password)
• Friends (user_id, friend_id)

what then is the limit to number of rows per table?

That depends on the MySQL table engine - MyISAM has a 4 GB limit; InnoDB is different. See this link for more details.

If it's better for me, I can create statistics tables by year (stats2010, stats2011).

Sounds like premature optimization.