Any settings which would let mysql do this in memory ? There is 8Gb Ram on the box dedicated for mysql.
my.cnf snippet as follows old_passwords=1 key_buffer_size=2048M max_allowed_packet=16M thread_stack=128K thread_cache_size=64 thread_concurrency=8 sort_buffer_size=32M join_buffer_size=3M read_buffer_size=16M query_cache_size=64M query_cache_limit=8M table_cache=100 max_connections=250 max_heap_table_size=64M myisam_sort_buffer_size=64M wait_timeout=3000 On Mon, Jun 9, 2008 at 3:54 PM, Gerald L. Clark < [EMAIL PROTECTED]> wrote: > Phil wrote: > >> I have a table as follows containing approx 1.5M rows. I pull data from it >> based on the CPID and insert into an 'overall' table. After breaking down >> the statement somewhat, an explain still gives a filesort. Is there any >> way >> to avoid a filesort for this query ? >> >> mysql> explain select a.cpid ,sum(a.metric1) >> ,sum(a.metric2),0,0,a.country,min(a.create_date),min(a.create_time),a.nick >> from boinc_user a group by a.cpid order by a.cpid; >> >> +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ >> | id | select_type | table | type | possible_keys | key | key_len | ref >> | >> rows | Extra | >> >> +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ >> | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL >> | >> 1443130 | Using temporary; Using filesort | >> >> +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+ >> 1 row in set (0.00 sec) >> >> >> CREATE TABLE `boinc_user` ( >> `proj` char(6) NOT NULL, >> `id` int(11) NOT NULL default '0', >> `stat_date` date NOT NULL default '0000-00-00', >> `nick` varchar(50) character set latin1 collate latin1_bin NOT NULL >> default '', >> `country` varchar(50) NOT NULL default '', >> `cpid` varchar(50) NOT NULL default '', >> `url` varchar(50) default NULL, >> `create_date` int(11) NOT NULL, >> `create_time` bigint(20) NOT NULL, >> `has_profile` char(1) NOT NULL, >> `team0` int(11) default NULL, >> `team1` int(11) default NULL, >> `metric1` double NOT NULL default '0', >> `metric2` double NOT NULL default '0', >> `metric3` double NOT NULL default '0', >> `metric4` double default NULL, >> `today` double default '0' >> PRIMARY KEY (`proj`,`id`), >> KEY `trank` (`proj`,`team0`,`metric1`,`id`), >> KEY `forstats` (`proj`,`metric1`,`id`), >> KEY `name` (`proj`,`id`), >> KEY `racrank` (`proj`,`metric2`,`id`), >> KEY `cpid` (`cpid`,`proj`), >> KEY `today` (`proj`,`today`,`id`), >> KEY `prank` (`proj`,`projrank0`,`id`) >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 >> >> Regards >> >> Phil >> >> >> > With no where clause, and aggregate functions, it is faster to do a full > table read, and the sort the aggregated results. > -- > Gerald L. Clark > Sr. V.P. Development > Supplier Systems Corporation > Unix since 1982 > Linux since 1992 > -- Help build our city at http://free-dc.myminicity.com !
