Skip navigation links

MySQL Forums :: Performance :: speed up query


Advanced Search

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 2964 Shabbir Ahmed 04/18/2010 06:42AM
Re: speed up query 1131 laptop alias 04/18/2010 04:04PM
Re: speed up query 1125 Rick James 04/18/2010 05:40PM
Re: speed up query 981 Shabbir Ahmed 04/27/2010 02:29PM
Re: speed up query 1025 Rick James 04/27/2010 07:59PM
Re: speed up query 958 Shabbir Ahmed 04/28/2010 12:12AM
Re: speed up query 1149 Rick James 04/28/2010 06:22AM
Re: speed up query 1104 Shabbir Ahmed 04/29/2010 01:03AM
Re: speed up query 949 Shabbir Ahmed 04/29/2010 02:17AM
Re: speed up query 1010 Rick James 04/29/2010 08:19AM
Re: speed up query 955 Rick James 04/29/2010 09:18AM
Re: speed up query 957 Shabbir Ahmed 04/30/2010 12:30AM
Re: speed up query 1043 Rick James 04/30/2010 07:47AM
Re: speed up query 1076 Shabbir Ahmed 04/30/2010 08:21AM
Re: speed up query 994 Rick James 04/30/2010 10:08AM
Re: speed up query 930 Shabbir Ahmed 05/01/2010 02:34AM
Re: speed up query 960 Rick James 05/01/2010 09:54AM
Re: speed up query 874 Shabbir Ahmed 05/01/2010 10:48AM
Re: speed up query 925 Rick James 05/01/2010 02:20PM
Re: speed up query 974 Shabbir Ahmed 05/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.