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
3680
April 18, 2010 06:42AM
1357
April 18, 2010 04:04PM
1335
April 18, 2010 05:40PM
1162
April 27, 2010 02:29PM
1233
April 27, 2010 07:59PM
1160
April 28, 2010 12:12AM
1366
April 28, 2010 06:22AM
1296
April 29, 2010 01:03AM
1211
April 29, 2010 02:17AM
1192
April 29, 2010 08:19AM
1209
April 29, 2010 09:18AM
1129
April 30, 2010 12:30AM
1282
April 30, 2010 07:47AM
1319
April 30, 2010 08:21AM
1205
April 30, 2010 10:08AM
1143
May 01, 2010 02:34AM
1132
May 01, 2010 09:54AM
1132
May 01, 2010 10:48AM
1154
May 01, 2010 02:20PM
1163
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.