| MySQL table count limit and related performance? [message #14] |
Fri, 11 August 2006 18:05  |
|
Hi, I am building a website that will have a large number of users, each with a "personal messageboard" feature. I want to know if it would be more efficient to give each user a dedicated table for storing their own data?
What is the performance impact of having many thousands of tables in MySQL?
Thanks,
Geoffrey Lee
|
|
|
| Re: MySQL table count limit and related performance? [message #15 is a reply to message #14 ] |
Fri, 11 August 2006 18:14  |
 Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Hi,
I would not do that. Thosands of tables would be fine, you however might need to increase table_cache and open_file_limit and use file system which handle such number of files well or put them into different directories.
However most users are likely to have only few messages which will be resource waste both on disk and in memory.
What I would do is to use many-many relashionship. Ie you have many tables and many users can be stored in the same table.
Great thing with such approach - you can flex it on your own will. Some larger users may get dedicated table while smaller users may be stored 100 users in the same table.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
