MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Optimisation of a slow query
Posted by: Gaurav Bhuyan
Date: September 19, 2011 01:01AM

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 :(

Options: ReplyQuote


Subject
Views
Written By
Posted
3327
September 13, 2011 03:26AM
1182
September 16, 2011 10:21PM
Re: Query Optimisation of a slow query
1096
September 19, 2011 01:01AM
1009
September 20, 2011 09:20PM
907
September 21, 2011 12:21AM
970
September 21, 2011 02:38AM
946
September 22, 2011 12:03AM
950
September 20, 2011 01:51AM


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.