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
