I want to speed up the following query as i add 7 million entries to dsl_data table every month and now after 6 months it takes too much time to execute, currently i move data to another table after every few months to speed up this query but i am looking for other ways for it, i was thinking to use partitioning on dsl_data.time or move this field to int and use unix timestamps, kindly help me fix it.
Thanku,
CREATE TABLE `dsl_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`port_id` int(11) DEFAULT NULL,
`snrup` decimal(5,2) DEFAULT NULL,
`snrdown` decimal(5,2) DEFAULT NULL,
`attnup` decimal(5,2) DEFAULT NULL,
`attndown` decimal(5,2) DEFAULT NULL,
`uprate` int(3) DEFAULT NULL,
`downrate` int(3) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `i_dsl_data_attnup` (`attnup`),
KEY `id_dsl_data_time` (`time`),
KEY `idx_dsl_data_port_id` (`port_id`)
) ENGINE=InnoDB AUTO_INCREMENT=133606144 DEFAULT CHARSET=latin1 |
CREATE TABLE `dslam_ports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dslam_id` int(11) DEFAULT NULL,
`dslam_slot` int(3) DEFAULT NULL,
`dslam_ports` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_dslam_id` (`dslam_id`),
KEY `i_dslam_ports_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=194342 DEFAULT CHARSET=latin1
CREATE TABLE `site_ip` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`did` int(11) DEFAULT NULL,
`ip` varchar(15) DEFAULT NULL,
`community` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_site_ip_id` (`id`),
KEY `idx_site_ip_did` (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=533 DEFAULT CHARSET=latin1
CREATE TABLE `site` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_name` varchar(200) DEFAULT NULL,
`frames` int(3) DEFAULT NULL,
`ports` int(11) DEFAULT NULL,
`dslam_vendor` varchar(200) DEFAULT NULL,
`dslam_ring` varchar(50) DEFAULT NULL,
`dslam_city` varchar(100) DEFAULT NULL,
`dslam_region` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=562 DEFAULT CHARSET=latin1
mysql> select avg(snrup) as snrup,avg(snrdown) as snrdown,site_ip.id,dslam_region from dsl_data,dslam_ports,site_ip,site where site.dslam_region='itr'
-> and site_ip.did=site.id and dslam_ports.dslam_id=site_ip.id and dsl_data.port_id=dslam_ports.id and dsl_data.time between "2010-01-01" and "2010-02-01" group by site.id order by snrdown desc;
+------------+------------+-----+--------------+
| snrup | snrdown | id | dslam_region |
+------------+------------+-----+--------------+
| 258.543959 | 277.678890 | 424 | ITR |
| 299.794704 | 275.983741 | 419 | ITR |
| 98.845763 | 250.653217 | 67 | ITR |
| 272.386450 | 240.365653 | 422 | ITR |
| 105.711462 | 219.376812 | 58 | ITR |
| 192.682222 | 215.071852 | 80 | ITR |
| 97.576903 | 199.903596 | 64 | ITR |
| 120.958942 | 196.567716 | 55 | ITR |
| 113.335225 | 184.550580 | 61 | ITR |
| 155.889122 | 178.692587 | 84 | ITR |
| 115.874015 | 174.052703 | 74 | ITR |
| 151.389199 | 172.705711 | 70 | ITR |
+------------+------------+-----+--------------+
12 rows in set (34.91 sec)
mysql> explain select avg(snrup) as snrup,avg(snrdown) as snrdown,site_ip.id,dslam_region from dsl_data,dslam_ports,site_ip,site where site.dslam_region='itr'
-> and site_ip.did=site.id and dslam_ports.dslam_id=site_ip.id and dsl_data.port_id=dslam_ports.id and dsl_data.time between "2010-01-01" and "2010-02-01" group by site.id order by snrdown desc;
+----+-------------+-------------+------+----------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+
| 1 | SIMPLE | site | ALL | PRIMARY | NULL | NULL | NULL | 325 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | site_ip | ref | PRIMARY,idx_site_ip_id,idx_site_ip_did | idx_site_ip_did | 5 | dslops.site.id | 1 | Using where; Using index |
| 1 | SIMPLE | dslam_ports | ref | PRIMARY,i_dslam_id,i_dslam_ports_id | i_dslam_id | 5 | dslops.site_ip.id | 256 | Using where; Using index |
| 1 | SIMPLE | dsl_data | ref | id_dsl_data_time,idx_dsl_data_port_id | idx_dsl_data_port_id | 5 | dslops.dslam_ports.id | 56 | Using where |
+----+-------------+-------------+------+----------------------------------------+----------------------+---------+-----------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)
mysql> select count(id) from dsl_data;
+-----------+
| count(id) |
+-----------+
| 24846010 |
+-----------+
1 row in set (11.55 sec)