Re: how to avoid using temporary and file sort? - Jan Theodore Galkowski - com.mysql.lists.win32

4 messages in com.mysql.lists.win32Re: how to avoid using temporary and ...

From	Sent On	Attachments
Ilavajuthy Palanisamy	13 May 2006 23:07
Jan Theodore Galkowski	14 May 2006 09:43
Jan Theodore Galkowski	14 May 2006 10:00
Ilavajuthy Palanisamy	14 May 2006 10:46

Subject:	Re: how to avoid using temporary and file sort?
From:	Jan Theodore Galkowski (jtga...@alum.mit.edu)
Date:	05/14/2006 10:00:40 AM
List:	com.mysql.lists.win32

The storage engine

alter table fs type=isam ;

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

- jtg

Try [it] again after doing:

alter table fs type=innodb ;

-jtg

On Sat, 13 May 2006 23:07:29 -0700, "Ilavajuthy Palanisamy" <ipal...@consentry.com> 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;

+----+-------------+-------+-------+----------------------+------------- ---------+---------+------+--------+------------------------------------ ----------+

+----+-------------+-------+-------+----------------------+------------- ---------+---------+------+--------+------------------------------------ ----------+

+----+-------------+-------+-------+----------------------+------------- ---------+---------+------+--------+------------------------------------ ----------+

1 row in set (0.00 sec)

mysql> explain select userid, sum(bytesin) from fs group by userid limit 10;

+----+-------------+-------+-------+---------------+-----------------+-- -------+------+--------+-------+

+----+-------------+-------+-------+---------------+-----------------+-- -------+------+--------+-------+

| 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]