how to avoid using temporary and file sort?
how to avoid using temporary and file sort?
am 14.05.2006 08:07:29 von Ilavajuthy Palanisamy
------_=_NextPart_001_01C6771C.AFA02CDC
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
=20
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?
=20
| 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=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
+-------+--------------------------------------------------- ------------
------------------------------------------------------------ ------------
-------------------------------------------------
------------------------------------------------------------ ------------
------------------------------------------------------------ ------------
-------------------------------------------------
------------------------------------------------------------ ------------
------------------------------------------------------------ ------------
-------------------------------------------------
------------------------------------------------------------ ------------
------------------------------------------------------------ ------------
-------------------------------------------------
------------------------------------------------------------ ------------
------------------------------------------------------------ ------------
-------------------------------------------------
------------------------------------------------------------ ------------
---------------+
1 row in set (0.00 sec)
=20
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)
=20
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)
=20
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
=20
=20
=20
Ila.
=20
=20
------_=_NextPart_001_01C6771C.AFA02CDC--
Re: how to avoid using temporary and file sort?
am 14.05.2006 18:43:42 von Jan Theodore Galkowski
Try in again after doing:
alter table fs type=3Dinnodb ;
-jtg
On Sat, 13 May 2006 23:07:29 -0700, "Ilavajuthy Palanisamy"
said:
> Hi,
>=20
>
>=20
> 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?
>=20
>
>=20
> | fs |CREATE TABLE `fs` (
>=20
> `id` bigint(20) NOT NULL default '0',
>=20
> `userId` bigint(20) NOT NULL default '0',
>=20
> `startTime` bigint(20) default NULL,
>=20
> `endTime` bigint(20) default NULL,
>=20
> `bytesIn` bigint(20) default NULL,
>=20
> `bytesOut` bigint(20) default NULL,
>=20
> `packetsIn` bigint(20) default NULL,
>=20
> `packetsOut` bigint(20) default NULL,
>=20
> `flowCount` bigint(20) default NULL,
>=20
> `lastUpdated` bigint(20) default NULL,
>=20
> `entryStatus` int(11) default NULL,
>=20
> `deviceId` int(11) NOT NULL default '0',
>=20
> `deviceSpecificId` bigint(20) NOT NULL default '0',
>=20
> `sourcePort` int(11) default NULL,
>=20
> `bandwidth` bigint(20) default NULL,
>=20
> `destIp` varchar(15) default NULL,
>=20
> `destPort` int(11) default NULL,
>=20
> `appName` varchar(255) default NULL,
>=20
> `appProtocol` int(11) default NULL,
>=20
> `appIdType` int(11) default NULL,
>=20
> `appCategory` varchar(30) default NULL,
>=20
> `flowDirection` int(11) default NULL,
>=20
> PRIMARY KEY (`id`),
>=20
> KEY `fs_userId_Index` (`userId`),
>=20
> KEY `fs_startTime_Index` (`startTime`),
>=20
> KEY `fs_lastUpdated_Index` (`lastUpdated`)
>=20
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
>=20
> +-------+--------------------------------------------------- ------------
> ------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> ------------------------------------------------------------ ------------
> ------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> ------------------------------------------------------------ ------------
> ------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> ------------------------------------------------------------ ------------
> ------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> ------------------------------------------------------------ ------------
> ------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> ------------------------------------------------------------ ------------
> ---------------+
>=20
> 1 row in set (0.00 sec)
>=20
>
>=20
> mysql> explain select userid, sum(bytesin) from fs where lastupdated>0
> group by userid limit 10;
>=20
> +----+-------------+-------+-------+----------------------+- ------------
> ---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra
> |
>=20
> +----+-------------+-------+-------+----------------------+- ------------
> ---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> | 1 | SIMPLE | fs | range | fs_lastUpdated_Index |
> fs_lastUpdated_Index | 9 | NULL | 629462 | Using where; Using
> temporary; Using filesort |
>=20
> +----+-------------+-------+-------+----------------------+- ------------
> ---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> 1 row in set (0.00 sec)
>=20
>
>=20
> mysql> explain select userid, sum(bytesin) from fs group by userid limit
> 10;
>=20
> +----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
>=20
> +----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> | 1 | SIMPLE | fs | index | NULL | fs_userId_Index |
> 8 | NULL | 629462 | |
>=20
> +----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> 1 row in set (0.00 sec)
>=20
>
>=20
> 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.
>=20
> The version of MYSQL is 4.1.0.15
>=20
>
>=20
>
>=20
>
>=20
> Ila.
>=20
>
>=20
>
>=20
--=20
Jan Theodore Galkowski (o°) =
jtgalkowski@alum.mit.edu
http://tinyurl.com/qty7d
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org
Re: how to avoid using temporary and file sort?
am 14.05.2006 19:01:03 von Jan Theodore Galkowski
The storage engine=20
alter table fs type=3Disam ;
also works if there's no InnoDB available for some reason.=20
- jtg
On Sun, 14 May 2006 12:43:42 -0400, "Jan Theodore Galkowski"
said:
> Try [it] again after doing:
>=20
> alter table fs type=3Dinnodb ;
>=20
> -jtg
>=20
> On Sat, 13 May 2006 23:07:29 -0700, "Ilavajuthy Palanisamy"
> said:
> > Hi,
> >=20
> >
> >=20
> > 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?
> >=20
> >
> >=20
> > | fs |CREATE TABLE `fs` (
> >=20
> > `id` bigint(20) NOT NULL default '0',
> >=20
> > `userId` bigint(20) NOT NULL default '0',
> >=20
> > `startTime` bigint(20) default NULL,
> >=20
> > `endTime` bigint(20) default NULL,
> >=20
> > `bytesIn` bigint(20) default NULL,
> >=20
> > `bytesOut` bigint(20) default NULL,
> >=20
> > `packetsIn` bigint(20) default NULL,
> >=20
> > `packetsOut` bigint(20) default NULL,
> >=20
> > `flowCount` bigint(20) default NULL,
> >=20
> > `lastUpdated` bigint(20) default NULL,
> >=20
> > `entryStatus` int(11) default NULL,
> >=20
> > `deviceId` int(11) NOT NULL default '0',
> >=20
> > `deviceSpecificId` bigint(20) NOT NULL default '0',
> >=20
> > `sourcePort` int(11) default NULL,
> >=20
> > `bandwidth` bigint(20) default NULL,
> >=20
> > `destIp` varchar(15) default NULL,
> >=20
> > `destPort` int(11) default NULL,
> >=20
> > `appName` varchar(255) default NULL,
> >=20
> > `appProtocol` int(11) default NULL,
> >=20
> > `appIdType` int(11) default NULL,
> >=20
> > `appCategory` varchar(30) default NULL,
> >=20
> > `flowDirection` int(11) default NULL,
> >=20
> > PRIMARY KEY (`id`),
> >=20
> > KEY `fs_userId_Index` (`userId`),
> >=20
> > KEY `fs_startTime_Index` (`startTime`),
> >=20
> > KEY `fs_lastUpdated_Index` (`lastUpdated`)
> >=20
> > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
> >=20
> > +-------+--------------------------------------------------- ------------
> > ------------------------------------------------------------ ------------
> > -------------------------------------------------
> >=20
> > ------------------------------------------------------------ ------------
> > ------------------------------------------------------------ ------------
> > -------------------------------------------------
> >=20
> > ------------------------------------------------------------ ------------
> > ------------------------------------------------------------ ------------
> > -------------------------------------------------
> >=20
> > ------------------------------------------------------------ ------------
> > ------------------------------------------------------------ ------------
> > -------------------------------------------------
> >=20
> > ------------------------------------------------------------ ------------
> > ------------------------------------------------------------ ------------
> > -------------------------------------------------
> >=20
> > ------------------------------------------------------------ ------------
> > ---------------+
> >=20
> > 1 row in set (0.00 sec)
> >=20
> >
> >=20
> > mysql> explain select userid, sum(bytesin) from fs where lastupdated>0
> > group by userid limit 10;
> >=20
> > +----+-------------+-------+-------+----------------------+- ------------
> > ---------+---------+------+--------+------------------------ ------------
> > ----------+
> >=20
> > | id | select_type | table | type | possible_keys | key
> > | key_len | ref | rows | Extra
> > |
> >=20
> > +----+-------------+-------+-------+----------------------+- ------------
> > ---------+---------+------+--------+------------------------ ------------
> > ----------+
> >=20
> > | 1 | SIMPLE | fs | range | fs_lastUpdated_Index |
> > fs_lastUpdated_Index | 9 | NULL | 629462 | Using where; Using
> > temporary; Using filesort |
> >=20
> > +----+-------------+-------+-------+----------------------+- ------------
> > ---------+---------+------+--------+------------------------ ------------
> > ----------+
> >=20
> > 1 row in set (0.00 sec)
> >=20
> >
> >=20
> > mysql> explain select userid, sum(bytesin) from fs group by userid limit
> > 10;
> >=20
> > +----+-------------+-------+-------+---------------+-------- ---------+--
> > -------+------+--------+-------+
> >=20
> > | id | select_type | table | type | possible_keys | key |
> > key_len | ref | rows | Extra |
> >=20
> > +----+-------------+-------+-------+---------------+-------- ---------+--
> > -------+------+--------+-------+
> >=20
> > | 1 | SIMPLE | fs | index | NULL | fs_userId_Index |
> > 8 | NULL | 629462 | |
> >=20
> > +----+-------------+-------+-------+---------------+-------- ---------+--
> > -------+------+--------+-------+
> >=20
> > 1 row in set (0.00 sec)
> >=20
> >
> >=20
> > 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.
> >=20
> > The version of MYSQL is 4.1.0.15
> >=20
[snip]
--=20
Jan Theodore Galkowski (o°) =
jtgalkowski@alum.mit.edu
http://tinyurl.com/qty7d
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org
RE: how to avoid using temporary and file sort?
am 14.05.2006 19:47:01 von Ilavajuthy Palanisamy
I tried changing type to innodb it didn't help.
-----Original Message-----
From: Jan Theodore Galkowski [mailto:jtgalkowski@alum.mit.edu]=20
Sent: Sunday, May 14, 2006 9:44 AM
To: win32@lists.mysql.com
Cc: Ilavajuthy Palanisamy
Subject: Re: how to avoid using temporary and file sort?
Try in again after doing:
alter table fs type=3Dinnodb ;
-jtg
On Sat, 13 May 2006 23:07:29 -0700, "Ilavajuthy Palanisamy"
said:
> Hi,
>=20
> =20
>=20
> 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?
>=20
> =20
>=20
> | fs |CREATE TABLE `fs` (
>=20
> `id` bigint(20) NOT NULL default '0',
>=20
> `userId` bigint(20) NOT NULL default '0',
>=20
> `startTime` bigint(20) default NULL,
>=20
> `endTime` bigint(20) default NULL,
>=20
> `bytesIn` bigint(20) default NULL,
>=20
> `bytesOut` bigint(20) default NULL,
>=20
> `packetsIn` bigint(20) default NULL,
>=20
> `packetsOut` bigint(20) default NULL,
>=20
> `flowCount` bigint(20) default NULL,
>=20
> `lastUpdated` bigint(20) default NULL,
>=20
> `entryStatus` int(11) default NULL,
>=20
> `deviceId` int(11) NOT NULL default '0',
>=20
> `deviceSpecificId` bigint(20) NOT NULL default '0',
>=20
> `sourcePort` int(11) default NULL,
>=20
> `bandwidth` bigint(20) default NULL,
>=20
> `destIp` varchar(15) default NULL,
>=20
> `destPort` int(11) default NULL,
>=20
> `appName` varchar(255) default NULL,
>=20
> `appProtocol` int(11) default NULL,
>=20
> `appIdType` int(11) default NULL,
>=20
> `appCategory` varchar(30) default NULL,
>=20
> `flowDirection` int(11) default NULL,
>=20
> PRIMARY KEY (`id`),
>=20
> KEY `fs_userId_Index` (`userId`),
>=20
> KEY `fs_startTime_Index` (`startTime`),
>=20
> KEY `fs_lastUpdated_Index` (`lastUpdated`)
>=20
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
>=20
> =
+-------+--------------------------------------------------- ------------
> =
------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> =
------------------------------------------------------------ ------------
> =
------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> =
------------------------------------------------------------ ------------
> =
------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> =
------------------------------------------------------------ ------------
> =
------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> =
------------------------------------------------------------ ------------
> =
------------------------------------------------------------ ------------
> -------------------------------------------------
>=20
> =
------------------------------------------------------------ ------------
> ---------------+
>=20
> 1 row in set (0.00 sec)
>=20
> =20
>=20
> mysql> explain select userid, sum(bytesin) from fs where lastupdated>0
> group by userid limit 10;
>=20
> =
+----+-------------+-------+-------+----------------------+- ------------
> =
---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra
> |
>=20
> =
+----+-------------+-------+-------+----------------------+- ------------
> =
---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> | 1 | SIMPLE | fs | range | fs_lastUpdated_Index |
> fs_lastUpdated_Index | 9 | NULL | 629462 | Using where; Using
> temporary; Using filesort |
>=20
> =
+----+-------------+-------+-------+----------------------+- ------------
> =
---------+---------+------+--------+------------------------ ------------
> ----------+
>=20
> 1 row in set (0.00 sec)
>=20
> =20
>=20
> mysql> explain select userid, sum(bytesin) from fs group by userid =
limit
> 10;
>=20
> =
+----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
>=20
> =
+----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> | 1 | SIMPLE | fs | index | NULL | fs_userId_Index |
> 8 | NULL | 629462 | |
>=20
> =
+----+-------------+-------+-------+---------------+-------- ---------+--
> -------+------+--------+-------+
>=20
> 1 row in set (0.00 sec)
>=20
> =20
>=20
> 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.
>=20
> The version of MYSQL is 4.1.0.15
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> Ila.
>=20
> =20
>=20
> =20
>=20
--=20
Jan Theodore Galkowski (o°) =20
jtgalkowski@alum.mit.edu
http://tinyurl.com/qty7d
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org