how to avoid using temporary and file sort?
"Ilavajuthy Palanisamy" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.windows |
|---|---|
| Message-ID | <52BBA75459915749B68F93B604B636CD0159E359@neptune.TidalNetworks.net> |
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.