Large table designissue

[omkar prabhu]

I have a table growing immensely every day , currently it has around 18777410 records.
Our site is available 24x7 and we are using mysql replication to distrubute load on the database server ( a master and single slave combination).
It is almost two years we running the application on mysql 5.0.45 everything seems to fine, but now we are facing problem with maintaining this huge table.
Following are key problem areas:
a) Running aggregate queries takes more than 5 minutes, which is not acceptable with respect to site performane
b) Adding index is not possible as it takes more than 3/4 hours which result in site downtime
c) command like optimize and analyze cant be run as it is online application and it will result in locking of this important table.


Can any one suggest possible approach to solve this problem, we are thinking of trying following approach
a) Have seperate table for having archive data,so query requiring complete data, will need to be fire two queries or have union on archive data and live data
b) Upgrade to mysql 5.1 and see having partitioning on this table helps
c) have mutliple database slave server to hit queries on most used use cases on this table.

Let me know if anyone faced similar problem and how to go about solving it