0

I'm working on an app that will have messages exchange and which have a database with several tables to all users data and preferences but I got thoughtful about the table of messages. I will have about 10k users that will exchange about 500k messages per month.

I have saw some questions in concern of this but are about of tables to store preferences and data, what I consider very different cases. So I was building the logic in this way, creating a table for each user, in a database created only for these message tables, so I can find the messages faster.

I thought: there would be 500k messages to query per month against 40~50 messages per month if having a table for each user.

What do you guys think about this?

  • 3
    It seems a lot simpler to just have a single table for all messages for all users. – JacquesB Apr 25 at 19:27
  • Table size has minimal impact on retrieval speed when the table is properly indexed. You need to read up on indexing and how it works. – GrandmasterB Apr 25 at 20:41
4

500K is not a large number of records for a relational database. Just use a single table, index the UserID fields, and you shouldn't have to worry about performance.

It is more likely that you'll take a performance hit on the added complexity of one table per user.

Further reading
10.3 Limits on Table Size

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.