Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
in a web application where people talk to each other , there will be a lot of messages , suppose for simplicity that each row in a table is a message ,
do you think if a have 5 million messages that one table is enough for them all ? or should i have 5 tables of one million each ?
the server used will be a simple vps or small dedicated server with 8Gb of ram ,two cores processor 2.5Ghz frequency , ssd ,
Yes, one table is probably enough. You don't usually gain anything by splitting the messages into multiple tables, i.e. horizontal partitioning. On the contrary, it will usually make your queries more complex and slower.
However, horizontal partitioning does make each index smaller, thereby potentially resulting in faster queries provided your data is segregated into e.g. different countries or regions, and you have an obvious, robust, implicit way to identify in which table a particular row will be found without first needing to search the index.
But in most cases, a single table is what you want.
