Partitioning on existing table

[Eyrique Wong]

Hi, i got an table that has about 60k rows of data is inserted DAILY, and the number of insertion is increasing by 1-200 daily.

The table now is very heavy when we are trying to do even some simple query.

And what I'm thinking of doing it is to do partitioning.

Is it possible to do partitioning on a table that already has data?
And how do i do the partitioning? I'm thinking partitioning by month, so it will create a partition every month. Is it possible?

Please advice. Thanks a lot!

Here's some details:
SHOW CREATE TABLE

CREATE TABLE `MT` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `shortcode` varchar(10) NOT NULL default '',
  `moperator` varchar(10) NOT NULL default '',
  `momsgid` varchar(20) NOT NULL default '',
  `sender` varchar(20) NOT NULL default '',
  `mphone` varchar(20) NOT NULL default '',
  `text` text NOT NULL,
  `price` varchar(10) NOT NULL default '',
  `mtmsgid` varchar(20) NOT NULL default '',
  `keyword` varchar(10) NOT NULL default '',
  `send_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `dn_status` varchar(5) NOT NULL default '',
  `dn_receive_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `mphone` (`mphone`),
  KEY `mtmsgid` (`mtmsgid`),
  KEY `send_date` (`send_date`),
  KEY `check_dn` (`mphone`,`keyword`,`send_date`,`dn_status`)
) ENGINE=InnoDB AUTO_INCREMENT=99285647 DEFAULT CHARSET=utf8

SHOW TABLE STATUS

+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+
| Name | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                 |
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+
| MT   | InnoDB |      10 | Compact    | 42923327 |            311 | 13391396864 |               0 |   8224014336 |         0 |       99288830 | 2009-10-24 04:51:28 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 1638400 kB | 
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------+

I'm looping a list of phone numbers (currently around 260k) to run the following query:

select count(*) as total from MT where mphone = 'xxxxx'
and send_date > '2009-09-01'
and dn_status = '123'

Edited 1 time(s). Last edit at 11/06/2009 01:18AM by Eyrique Wong.