RE: how to avoid using temporary and file sort?

I tried changing type to innodb it didn't help.



-----Original Message-----
From: Jan Theodore Galkowski [mailto:[email protected]] 
Sent: Sunday, May 14, 2006 9:44 AM
To: [email protected]
Cc: Ilavajuthy Palanisamy
Subject: Re: how to avoid using temporary and file sort?

Try in 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
> 
>  
> 
>  
> 
>  
> 
> Ila.
> 
>  
> 
>  
> 
-- 
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]