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'
btw, the 60k is not the total rows in my table, is 60k rows of data insert into table daily.