1 million records is not a lot at all if you have it indexed right. I've worked with systems where tables grew by 30 million records per month. Although we only kept about 300 million records at any one time.
It's not really the size or record count you need to worry about, but the activity. You may end up with locking issues if you are using MyISAM. Although if you are probably not doing a lot of deletes and updates, just mainly inserts so it may not be able. InnoDB won't have locking issues, but could have performance issues if don't have enough memory. You can use partitions or merge tables to split your data, but splitting your tables into different databases isn't really going to help you. Splitting your mail into inbox and outbox may be helpful since you inbox would probably only ever have records added to it. Whereas the outbox would have a lot of activity (inserts and deletes). Brent Baisley On Fri, Jun 26, 2009 at 11:25 AM, fa so<fak...@yahoo.com> wrote: > 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
