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
3363
September 13, 2011 03:26AM
1196
September 16, 2011 10:21PM
Re: Query Optimisation of a slow query
1119
September 19, 2011 01:01AM
1020
September 20, 2011 09:20PM
927
September 21, 2011 12:21AM
985
September 21, 2011 02:38AM
956
September 22, 2011 12:03AM
964
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.