Re: speed up query

[Shabbir Ahmed]

today i made another replica of dsl_data table with only snrup and snrdown and look at the result, now what u sujjest! and the replica table has given this result without any indexing.

mysql> select count(snrdown) from dsl_data,dslam_ports where dslam_ports.dslam_id='2' and dsl_data.port_id=dslam_ports.id
-> and dsl_data.snrdown > 399;
+----------------+
| count(snrdown) |
+----------------+
| 31 |
+----------------+
1 row in set (43.21 sec)

mysql>
mysql>
mysql> select count(snrdown) from dsl_data_snr,dslam_ports where dslam_ports.dslam_id='2' and dsl_data_snr.port_id=dslam_ports.id
-> and dsl_data_snr.snrdown > 399
-> ;
+----------------+
| count(snrdown) |
+----------------+
| 31 |
+----------------+
1 row in set (13.29 sec)

mysql> show create table dsl_data;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dsl_data | 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 `id_dsl_data_time` (`time`),
KEY `idx_dsl_data_port_id` (`port_id`),
KEY `idx_dsl_data_time_portid` (`time`,`port_id`)
) ENGINE=InnoDB AUTO_INCREMENT=135872073 DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table dsl_data_snr;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dsl_data_snr | CREATE TABLE `dsl_data_snr` (
`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,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27131491 DEFAULT CHARSET=latin1 |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(id) from dsl_data;
+-----------+
| count(id) |
+-----------+
| 27111939 |
+-----------+
1 row in set (3 min 7.39 sec)

mysql> select count(id) from dsl_data_snr;
+-----------+
| count(id) |
+-----------+
| 27111939 |
+-----------+
1 row in set (6.06 sec)



Edited 1 time(s). Last edit at 05/02/2010 04:02AM by Shabbir Ahmed.