Hello
My selects still last several hours. Can anybody give me a hint which
parameters to tune when the main selects look like the following one on a
1.5GB big table with aprox 1 million affected rows?
join_buffer_size | 131072
key_buffer_size | 67104768
max_heap_table_size | 16777216
max_join_size | 4294967295
max_sort_length | 1024
max_write_lock_count | 4294967295
myisam_sort_buffer_size | 16777216
net_buffer_length | 16384
record_buffer | 2093056
sort_buffer | 2097144
table_cache | 128
thread_cache_size | 2
on a Intel PentiumII/233MHz with RAID5 and MySQL-3.23.33.
CREATE TABLE channel_traffic (
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
hostname varchar(15) NOT NULL default '',
interfacename varchar(30) NOT NULL default '',
in_bytes int(10) unsigned NOT NULL default '0',
out_bytes int(10) unsigned NOT NULL default '0',
in_uc_packets int(10) unsigned NOT NULL default '0',
out_uc_packets int(10) unsigned NOT NULL default '0',
in_mc_packets int(10) unsigned NOT NULL default '0',
out_mc_packets int(10) unsigned NOT NULL default '0',
PRIMARY KEY (timestamp,hostname,interfacename)
) TYPE=MyISAM PACK_KEYS=1;
select
'2001-03-05 11:11:11',
concat(hostname," ",interfacename) as channels_line,
sum(
channel_traffic.in_bytes + channel_traffic.out_bytes
) as raw_traffic,
sum(
channel_traffic.in_bytes + channel_traffic.out_bytes - 0.9 * (
channel_traffic.in_uc_packets + channel_traffic.out_uc_packets +
channel_traffic.in_mc_packets + channel_traffic.out_mc_packets
)
) as adj_traffic,
min(channel_traffic.timestamp) as start,
max(channel_traffic.timestamp) as end,
count(*)
from
channel_traffic
where
channel_traffic.timestamp between "2001-03-01" and "2001-03-03"
and
channel_traffic.timestamp between "2000-01-01" and "2999-12-31"
group by
channels_line
;
explain ....
+-----------------+-------+---------------+---------+---------+------+---------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-----------------+-------+---------------+---------+---------+------+---------+-----------------------------+
| channel_traffic | range | PRIMARY | PRIMARY | 8 | NULL | 1636199 | where
|used; Using temporary |
+-----------------+-------+---------------+---------+---------+------+---------+-----------------------------+
thanks,
-christian-
--
Christian Hammers WESTEND GmbH - Aachen und Dueren Tel 0241/701333-0
[EMAIL PROTECTED] Internet & Security for Professionals Fax 0241/911879
WESTEND ist CISCO Systems Partner - Premium Certified
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
