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 3040 Shabbir Ahmed 04/18/2010 06:42AM
Re: speed up query 1161 laptop alias 04/18/2010 04:04PM
Re: speed up query 1153 Rick James 04/18/2010 05:40PM
Re: speed up query 998 Shabbir Ahmed 04/27/2010 02:29PM
Re: speed up query 1035 Rick James 04/27/2010 07:59PM
Re: speed up query 969 Shabbir Ahmed 04/28/2010 12:12AM
Re: speed up query 1166 Rick James 04/28/2010 06:22AM
Re: speed up query 1116 Shabbir Ahmed 04/29/2010 01:03AM
Re: speed up query 971 Shabbir Ahmed 04/29/2010 02:17AM
Re: speed up query 1034 Rick James 04/29/2010 08:19AM
Re: speed up query 988 Rick James 04/29/2010 09:18AM
Re: speed up query 972 Shabbir Ahmed 04/30/2010 12:30AM
Re: speed up query 1071 Rick James 04/30/2010 07:47AM
Re: speed up query 1094 Shabbir Ahmed 04/30/2010 08:21AM
Re: speed up query 1018 Rick James 04/30/2010 10:08AM
Re: speed up query 942 Shabbir Ahmed 05/01/2010 02:34AM
Re: speed up query 965 Rick James 05/01/2010 09:54AM
Re: speed up query 901 Shabbir Ahmed 05/01/2010 10:48AM
Re: speed up query 941 Rick James 05/01/2010 02:20PM
Re: speed up query 982 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.