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 2988 Shabbir Ahmed 04/18/2010 06:42AM
Re: speed up query 1141 laptop alias 04/18/2010 04:04PM
Re: speed up query 1136 Rick James 04/18/2010 05:40PM
Re: speed up query 985 Shabbir Ahmed 04/27/2010 02:29PM
Re: speed up query 1030 Rick James 04/27/2010 07:59PM
Re: speed up query 963 Shabbir Ahmed 04/28/2010 12:12AM
Re: speed up query 1152 Rick James 04/28/2010 06:22AM
Re: speed up query 1109 Shabbir Ahmed 04/29/2010 01:03AM
Re: speed up query 959 Shabbir Ahmed 04/29/2010 02:17AM
Re: speed up query 1023 Rick James 04/29/2010 08:19AM
Re: speed up query 973 Rick James 04/29/2010 09:18AM
Re: speed up query 968 Shabbir Ahmed 04/30/2010 12:30AM
Re: speed up query 1055 Rick James 04/30/2010 07:47AM
Re: speed up query 1080 Shabbir Ahmed 04/30/2010 08:21AM
Re: speed up query 1006 Rick James 04/30/2010 10:08AM
Re: speed up query 932 Shabbir Ahmed 05/01/2010 02:34AM
Re: speed up query 962 Rick James 05/01/2010 09:54AM
Re: speed up query 888 Shabbir Ahmed 05/01/2010 10:48AM
Re: speed up query 927 Rick James 05/01/2010 02:20PM
Re: speed up query 977 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.