Re: how to avoid using temporary and file sort?

The storage engine 

   alter table fs type=isam ;

also works if there's no InnoDB available for some reason. 

 - jtg


On Sun, 14 May 2006 12:43:42 -0400, "Jan Theodore Galkowski"
<[email protected]> said:
> Try [it] again after doing:
> 
>   alter table fs type=innodb ;
> 
>  -jtg
> 
> On Sat, 13 May 2006 23:07:29 -0700, "Ilavajuthy Palanisamy"
> <[email protected]> said:
> > Hi,
> > 
> >  
> > 
> > I need a help in optimizing a query. The explain of the query shows
> > using temporary and file sort. Is there a way to avoid it?
> > 
> >  
> > 
> > | fs    |CREATE TABLE `fs` (
> > 
> >   `id` bigint(20) NOT NULL default '0',
> > 
> >   `userId` bigint(20) NOT NULL default '0',
> > 
> >   `startTime` bigint(20) default NULL,
> > 
> >   `endTime` bigint(20) default NULL,
> > 
> >   `bytesIn` bigint(20) default NULL,
> > 
> >   `bytesOut` bigint(20) default NULL,
> > 
> >   `packetsIn` bigint(20) default NULL,
> > 
> >   `packetsOut` bigint(20) default NULL,
> > 
> >   `flowCount` bigint(20) default NULL,
> > 
> >   `lastUpdated` bigint(20) default NULL,
> > 
> >   `entryStatus` int(11) default NULL,
> > 
> >   `deviceId` int(11) NOT NULL default '0',
> > 
> >   `deviceSpecificId` bigint(20) NOT NULL default '0',
> > 
> >   `sourcePort` int(11) default NULL,
> > 
> >   `bandwidth` bigint(20) default NULL,
> > 
> >   `destIp` varchar(15) default NULL,
> > 
> >   `destPort` int(11) default NULL,
> > 
> >   `appName` varchar(255) default NULL,
> > 
> >   `appProtocol` int(11) default NULL,
> > 
> >   `appIdType` int(11) default NULL,
> > 
> >   `appCategory` varchar(30) default NULL,
> > 
> >   `flowDirection` int(11) default NULL,
> > 
> >   PRIMARY KEY  (`id`),
> > 
> >   KEY `fs_userId_Index` (`userId`),
> > 
> >   KEY `fs_startTime_Index` (`startTime`),
> > 
> >   KEY `fs_lastUpdated_Index` (`lastUpdated`)
> > 
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
> > 
> > +-------+---------------------------------------------------------------
> > ------------------------------------------------------------------------
> > -------------------------------------------------
> > 
> > ------------------------------------------------------------------------
> > ------------------------------------------------------------------------
> > -------------------------------------------------
> > 
> > ------------------------------------------------------------------------
> > ------------------------------------------------------------------------
> > -------------------------------------------------
> > 
> > ------------------------------------------------------------------------
> > ------------------------------------------------------------------------
> > -------------------------------------------------
> > 
> > ------------------------------------------------------------------------
> > ------------------------------------------------------------------------
> > -------------------------------------------------
> > 
> > ------------------------------------------------------------------------
> > ---------------+
> > 
> > 1 row in set (0.00 sec)
> > 
> >  
> > 
> > mysql> explain select userid, sum(bytesin) from fs where lastupdated>0
> > group by userid limit 10;
> > 
> > +----+-------------+-------+-------+----------------------+-------------
> > ---------+---------+------+--------+------------------------------------
> > ----------+
> > 
> > | id | select_type | table | type  | possible_keys        | key
> > | key_len | ref  | rows   | Extra
> > |
> > 
> > +----+-------------+-------+-------+----------------------+-------------
> > ---------+---------+------+--------+------------------------------------
> > ----------+
> > 
> > |  1 | SIMPLE      | fs    | range | fs_lastUpdated_Index |
> > fs_lastUpdated_Index |       9 | NULL | 629462 | Using where; Using
> > temporary; Using filesort |
> > 
> > +----+-------------+-------+-------+----------------------+-------------
> > ---------+---------+------+--------+------------------------------------
> > ----------+
> > 
> > 1 row in set (0.00 sec)
> > 
> >  
> > 
> > mysql> explain select userid, sum(bytesin) from fs group by userid limit
> > 10;
> > 
> > +----+-------------+-------+-------+---------------+-----------------+--
> > -------+------+--------+-------+
> > 
> > | id | select_type | table | type  | possible_keys | key             |
> > key_len | ref  | rows   | Extra |
> > 
> > +----+-------------+-------+-------+---------------+-----------------+--
> > -------+------+--------+-------+
> > 
> > |  1 | SIMPLE      | fs    | index | NULL          | fs_userId_Index |
> > 8 | NULL | 629462 |       |
> > 
> > +----+-------------+-------+-------+---------------+-----------------+--
> > -------+------+--------+-------+
> > 
> > 1 row in set (0.00 sec)
> > 
> >  
> > 
> > If I add a where clause it uses temporary and filesort. If the table is
> > having 10 million records, then it takes hours to complete this query.
> > 
> > The version of MYSQL is 4.1.0.15
> > 

[snip]


-- 
Jan Theodore Galkowski   (o°)                    
 [email protected]
 http://tinyurl.com/qty7d



-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]