Hello, I am running a rather simple query on a merge table that seems to be taking much longer than it should.
First let me show the table status of the tables I have merged into table 'blah': Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment accounting_2002W29 MyISAM Fixed 4 435 1740 1868310773759 3072 0 NULL 2002-09-19 21:11:30 2002-09-20 19:27:01 NULL accounting_2002W30 MyISAM Fixed 10 435 4350 1868310773759 3072 0 NULL 2002-09-19 21:24:35 2002-09-20 18:37:51 NULL accounting_2002W31 MyISAM Fixed 1612741 435 701542335 1868310773759 97556480 0 NULL 2002-09-19 21:08:09 2002-09-20 19:45:32 NULL accounting_2002W32 MyISAM Fixed 2341152 435 1018401120 1868310773759 142214144 0 NULL 2002-09-19 21:08:15 2002-09-20 19:45:32 NULL accounting_2002W33 MyISAM Fixed 3204059 435 1393765665 1868310773759 195347456 0 NULL 2002-09-19 21:08:25 2002-09-20 19:45:32 NULL accounting_2002W34 MyISAM Fixed 3388325 435 1473921375 1868310773759 207994880 0 NULL 2002-09-19 21:08:43 2002-09-20 19:45:32 NULL accounting_2002W35 MyISAM Fixed 3322128 435 1445125680 1868310773759 204946432 0 NULL 2002-09-20 11:26:21 2002-09-20 19:45:32 NULL And the query I run takes very long: mysql> select count(distinct nas_ip) from blah; +------------------------+ | count(distinct nas_ip) | +------------------------+ | 116 | +------------------------+ 1 row in set (15 min 43.27 sec) I consider this very long because if I do the same query on one of the merged tables it is much faster: mysql> select count(distinct nas_ip) from accounting_2002W35; +------------------------+ | count(distinct nas_ip) | +------------------------+ | 96 | +------------------------+ 1 row in set (42.03 sec) Note I have restarted the server before each query to not let caching affect this. I don't understand why the merge table takes so long. It would be faster for me to issue a query for the nas_ip in each table, output this to a temporary table, and then do a select count(distinct) on this table. Why is the merge table showing bad performance on this query? Here are some more details. System: Linux 2.2.16 (Redhat 6.2) show create table accounting_2002W35; CREATE TABLE `accounting_2002W35` ( `acct_status_type` enum('Start','Stop') NOT NULL default 'Start', `user_name` char(80) NOT NULL default '', `nas_ip` char(15) NOT NULL default '', `acct_session_time` mediumint(8) unsigned default NULL, `acct_session_id` char(30) NOT NULL default '', `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `framed_ip` char(15) default NULL, `input_bytes` int(10) unsigned default NULL, `output_bytes` int(10) unsigned default NULL, `calling_station_id` char(80) default NULL, `called_station_id` char(80) default NULL, `baud_rate` mediumint(8) unsigned default NULL, `framed_protocol` char(20) default NULL, `class` char(80) default NULL, `visp_id` mediumint(8) unsigned default NULL, `country_id` tinyint(3) unsigned default NULL, `city_id` mediumint(8) unsigned default NULL, `provider_id` mediumint(8) unsigned default NULL, UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`), KEY `nas_ind` (`nas_ip`) ) TYPE=MyISAM (all accounting tables are the same as above) show create table blah; CREATE TABLE `blah` ( `acct_status_type` enum('Start','Stop') NOT NULL default 'Start', `user_name` char(80) NOT NULL default '', `nas_ip` char(15) NOT NULL default '', `acct_session_time` mediumint(8) unsigned default NULL, `acct_session_id` char(30) NOT NULL default '', `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `framed_ip` char(15) default NULL, `input_bytes` int(10) unsigned default NULL, `output_bytes` int(10) unsigned default NULL, `calling_station_id` char(80) default NULL, `called_station_id` char(80) default NULL, `baud_rate` mediumint(8) unsigned default NULL, `framed_protocol` char(20) default NULL, `class` char(80) default NULL, `visp_id` mediumint(8) unsigned default NULL, `country_id` tinyint(3) unsigned default NULL, `city_id` mediumint(8) unsigned default NULL, `provider_id` mediumint(8) unsigned default NULL, UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`), KEY `nas_ind` (`nas_ip`) ) TYPE=MRG_MyISAM UNION=(accounting_2002W29,accounting_2002W30,accounting_2002W31,accounting_2002W32,accounting_2002W33,accounting_2002W34,accounting_2002W35) Thanks for any help, Viraj. --------------------------------------------------------------------- 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