I have came across a situation where app generates around 50Million records each year.. We are using mysql 4.1 and its not possible to upgrade at this time.. The table structure is very simple with few columns.. The problem is how to handle this many records in one table and still have decent performance.. It's a private messaging system similar to one we have here on DP The requirement is to support the app to scale to 200K users.. each user may send around 300 messages a year, Messages are never deleted from DB Table structure would be some thing like below - message_folders -- id -- user_id (FK) -- name varchar2 -- created_at timestap - Conversation -- id -- folder_id (FK) - messages -- id -- author_id (FK) -- Recipant (FK) -- Subject varchar2 -- Body varchar2 -- attchment_name -- author_deleted boolean - Recipant_deleted boolean -- date_sent timestamp - Conversation_messages -- conversation_id -- message_id - unique Can any one provide inputs, ideas on how this situation can be handled without partitioning table. As far as I know 4.1 does not support partitioning... Any suggestion on Table structure or any thing is welcome
You can use merge tables to split the data across several tables and still retrieval is fast.. http://dev.mysql.com/doc/refman/4.1/en/merge-storage-engine.html