>> Would it be beneficial to divide this database tables >> across different databases where each database holds some tables?
If you are planning to scale to large amounts of database activity in the future then yes, this will help very much. If you split your tables into several logical databases and ensure there are no cross-database joins; in the future you will be able to scale by moving some of the logical databases onto separate physical hosts relatively easily. Of course tuning your SQL/mysql configuration/operating system/indexes properly will afford you the best scalability rather than throwing hardware at it. Cheers, Andrew -----Original Message----- From: fa so [mailto:fak...@yahoo.com] Sent: 26 June 2009 16:26 To: mysql@lists.mysql.com Subject: Growing database & Performance I have a website where my database is continuously growing. And I started being worried about performance. I have a couple of questions, and I would appreciate it very much if you can elaborate on them. - I have about 70 tables in the same database. some of them are becoming very large (over 1 million record), and I guess in a couple of months some of them will be double in size, like the mailbox table. Would it be beneficial to divide this database tables across different databases where each database holds some tables? - I was looking at MySQL table partition, and I would like to try it. I am not sure though what is the best way to do it, for example in my mailbox table, I have "senderId" and "receiverId" as keys and I query inbox and outbox using these keys.. If I want to paritition the table, what is the partion by method I should use? - do you think dividing my mailbox table into separate tables for inbox and outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages would be beneficial? - I am also guessing that my mailbox table will be holding 10s of millions of records in a year or two period.. and I am not sure about the best way to habdle such a table when it grows very much.. any ideas on how to plan for such a senario? I can imagine the best way would be to create many tables each holding a portion of the mailbox table while using MySQL partition on each of them... I am wondering though about the best way to map "senderId" and "receiverId" to the correct table thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
