Re: Query Optimisation of a slow query

[Gaurav Bhuyan]

Thanks for the reply!

Create table syntax :

CREATE TABLE IF NOT EXISTS `p_network_traffic_throughput_tmp1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`network_traffic_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`date_time` varchar(23) NOT NULL default '',
`load_in` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`load_out` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`a_date_time` varchar(23) NOT NULL default '',
PRIMARY KEY( `id` ),
UNIQUE KEY `traffic_id_date_time`( `network_traffic_id` , `date_time` ),
KEY `network_traffic_id`( `network_traffic_id` ),
KEY `date_time`( `date_time` ));


CREATE TABLE IF NOT EXISTS `p_network_traffic_throughput_tmp2` (
`id` INT(11) UNSIGNED NOT NULL,
`network_traffic_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`date_time` varchar(23) NOT NULL default '',
`load_in` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`load_out` INT(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY( `id` ),
UNIQUE KEY `traffic_id_date_time`( `network_traffic_id` , `date_time` ),
KEY `network_traffic_id`( `network_traffic_id` ),
KEY `date_time`( `date_time` ));

CREATE
TABLE p_network_traffic_bearer
(
id INT NOT NULL AUTO_INCREMENT,
network_traffic_id INT DEFAULT '0' NOT NULL,
date_time CHAR(23) NOT NULL,
bearer enum('unknown','csdgsm','wcdma','lan','cdma2000','gprs','hscsd','edgegprs','wlan',
'bluetooth','virtual','virtualvpn','wcdmacsd','extcsd','extwcdma','extlan','extcdma2000',
'extgprs','exthscsd','extedgegprs','extwlan','extbluetooth','extwcdmacsd') DEFAULT
'Unknown' NOT NULL,
latitude DOUBLE(12,6),
longitude DOUBLE(12,6),
altitude DOUBLE(12,6),
PRIMARY KEY (id),
FOREIGN KEY (network_traffic_id) REFERENCES p_network_traffic (id) ON
DELETE
CASCADE ON
UPDATE
CASCADE,
INDEX network_traffic_id (network_traffic_id),
INDEX date_time (date_time)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8


I have already posted the explain part in the first post. :)

Buffer sizes :

<code>

| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 2147483648 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 262144000 |
| join_buffer_size | 131072 |
| key_buffer_size | 4194304 |
| myisam_sort_buffer_size | 4194304 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF

</code>

I suspect that the sub query is taking time , but i
cant rewrite the query without the sub query.

I have a RAM of 20 GB.

Thanks a lot. This query is killing the application :(