MySQL table count limit and related performance?

[geofflee]

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

[Peter]

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.