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
