MySQL Forums
Forum List  »  Performance

speed up query
Posted by: Shabbir Ahmed
Date: April 18, 2010 06:42AM

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) 

Options: ReplyQuote


Subject
Views
Written By
Posted
speed up query
3709
April 18, 2010 06:42AM
1371
April 18, 2010 04:04PM
1350
April 18, 2010 05:40PM
1185
April 27, 2010 02:29PM
1251
April 27, 2010 07:59PM
1177
April 28, 2010 12:12AM
1383
April 28, 2010 06:22AM
1309
April 29, 2010 01:03AM
1227
April 29, 2010 02:17AM
1216
April 29, 2010 08:19AM
1224
April 29, 2010 09:18AM
1148
April 30, 2010 12:30AM
1329
April 30, 2010 07:47AM
1330
April 30, 2010 08:21AM
1218
April 30, 2010 10:08AM
1156
May 01, 2010 02:34AM
1146
May 01, 2010 09:54AM
1143
May 01, 2010 10:48AM
1167
May 01, 2010 02:20PM
1179
May 02, 2010 03:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.