Hi Phil, Since u dont have where condition, doing a full table scan is better.
in mysql, group by also does SORTING, as if you have specified an explicit "ORDER BY". So you can avoid ORDER BY and see if the select are same with both group by and order by. But still you would see a FILESORT in the explain plan. On 6/10/08, Phil <[EMAIL PROTECTED]> wrote: > > 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 ! >
