this is curious:
the table:
CREATE TABLE inbw_day_diffs_2001_Jan_02_10 (
hostname char(20) NOT NULL default '',
variable_name char(64) NOT NULL default '',
variable_index_value char(114) NOT NULL default '',
variable_index_label char(50) NOT NULL default '',
variable_result_value_numeric double NOT NULL default '0',
label_value char(255) NOT NULL default '',
reading_time int(10) unsigned NOT NULL default '0',
PRIMARY KEY
(hostname,variable_name,variable_index_value,variable_index_label,reading_time),
KEY variable_name(variable_name),
KEY variable_index_value(variable_index_value),
KEY reading_time(reading_time)
) TYPE=MyISAM;
this statement:
create table my_test
select hostname,
variable_name,
variable_index_value,
variable_index_label,
AVG(variable_result_value_numeric),
COUNT(variable_result_value_numeric),
STD(variable_result_value_numeric),
MAX(variable_result_value_numeric),
MIN(variable_result_value_numeric),
label_value,
FLOOR(reading_time/4200)*4200 as reading_int
from inbw_day_diffs_2001_Jan_02_10
where reading_time < (FLOOR(979192800/4200)*4200)
group by hostname,
variable_name,
variable_index_value,
variable_index_label,
reading_int
creates a table of 57203 rows from a 487977 row table.
it creates a 33Meg tmp file and completes in 280 seconds.
this following statement, however does what i really want to do,
but it's unreasonable to run time-wise:
create table my_test
select hostname,
variable_name,
variable_index_value,
variable_index_label,
FLOOR(reading_time/4200)*4200 as reading_int,
AVG(variable_result_value_numeric),
COUNT(variable_result_value_numeric),
STD(variable_result_value_numeric),
MAX(variable_result_value_numeric),
MIN(variable_result_value_numeric),
SUBSTRING_INDEX(MAX(CONCAT(reading_time,'mydelim**-**',label_value)),'mydelim**-**',-1)
as foo
from inbw_day_diffs_2001_Jan_02_10
where reading_time < (FLOOR(979192800/4200)*4200)
group by hostname,
variable_name,
variable_index_value,
variable_index_label,
reading_int
pretty much doesn't complete in anywhere near 280 seconds, it
only has a 5.5Meg temp file in 194 seconds.
(really slow)
any thoughts/suggestions?
the only thing that's different is the SUBSTRING_INDEX(MAX(CONCAT()))
construct, and i can't immediately see why that slows it down so
much...
---------------------------------------------------------------------
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
