At 11:02 AM 6/26/2009, you wrote:
Hi Mike,
I am using MyISAM but I want to convert all tables to InnoDB.. but before
I make the conversion I am trying to have a solid plan on how to handle
everything
If it were me, I'd would create at least 2 tables, current and
archive. Since 95% of the activity will be with the current table, it will
be small and faster than trying to put all of the data into 1 table. If the
user wants to see the messages from more than a certain # of months (6mo or
1yr), then have a checkbox so he can display or search the archive. Every
month, copy the oldest month to the archive and delete it from the current
table. If you were using MyISAM you could use Merge tables for that and it
would handle this situation quite easily. Of course if you need
transactions, then you're going to have to use InnoDb.
Mike
--- On Fri, 6/26/09, mos <mo...@fastmail.fm> wrote:
From: mos <mo...@fastmail.fm>
Subject: Re: Growing database & Performance
To: mysql@lists.mysql.com
Date: Friday, June 26, 2009, 8:52 AM
At 10:25 AM 6/26/2009, you 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
>
Are you using MyISAM or InnoDb?
Mike
-- MySQL General Mailing List
For list archives: <http://lists.mysql.com/mysql>http://lists.mysql.com/mysql
To
unsubscribe:
<http://lists.mysql.com/mysql?unsub=fak...@yahoo.com>http://lists.mysql.com/mysql?unsub=fak...@yahoo..com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
