# trying to get rid of filesort

**URL:** <https://forums.percona.com/t/trying-to-get-rid-of-filesort/635>\
**Category:** Other MySQL® Questions\
**Created:** [February 14, 2008, 8:54pm UTC](https://forums.percona.com/t/trying-to-get-rid-of-filesort/635 "2008-02-14T20:54:31Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![fqaisrani](https://avatars.discourse-cdn.com/v4/letter/f/a6a055/32.png) [@fqaisrani](https://forums.percona.com/u/fqaisrani)\
**Post date:** [February 14, 2008, 8:54pm UTC](https://forums.percona.com/t/trying-to-get-rid-of-filesort/635/1 "2008-02-14T20:54:31Z")

</div>

Guys following is the table

CREATE TABLE `tbl_topic` (  
`topicId` mediumint(cool: unsigned NOT NULL auto\_increment,  
`forumId` smallint(5) unsigned NOT NULL default ‘0’,  
`forumName` char(35) NOT NULL default ‘’,  
`topicName` char(60) NOT NULL default ‘’,  
`creatorId` int(10) unsigned NOT NULL default ‘0’,  
`createdBy` char(50) NOT NULL default ‘’,  
`creatorClass` char(10) NOT NULL default ‘’,  
`createdAt` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`creatorIP` int(10) unsigned NOT NULL default ‘0’,  
`totalPosts` int(10) unsigned NOT NULL default ‘1’,  
`lastPosterId` int(10) unsigned NOT NULL default ‘0’,  
`lastPoster` char(50) NOT NULL default ‘’,  
`lastPosterClass` char(10) NOT NULL default ‘’,  
`lastPostTime` datetime NOT NULL default ‘0000-00-00 00:00:00’,  
`lastPosterIP` int(10) unsigned NOT NULL default ‘0’,  
`topicStatus` enum(‘active’,‘inactive’) NOT NULL default ‘active’,  
`accessLevel` tinyint(4) NOT NULL default ‘0’ COMMENT ‘0=all, 1=invited, 2=mod, 3=admin’,  
`is_pinned` tinyint(3) unsigned NOT NULL default ‘0’,  
`is_deleted` tinyint(3) unsigned NOT NULL default ‘0’,  
PRIMARY KEY (`topicId`),  
KEY `forumId` (`forumId`),  
KEY `topicStatus` (`topicStatus`),  
KEY `is_pinned` (`is_pinned`,`lastPostTime`),  
KEY `is_deleted` (`is_deleted`),  
KEY `lastPostTime` (`lastPostTime`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Some temp data

INSERT INTO `tbl_topic` VALUES (1, 1, ‘Announcements’, ‘This is test Title ‘’ " \ ©’, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:24:30’, 127, 1, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:24:30’, 127, ‘active’, 0, 0, 0);  
INSERT INTO `tbl_topic` VALUES (2, 1, ‘Announcements’, ‘This is 2nd Topic’, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:09’, 127, 1, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:09’, 127, ‘active’, 0, 0, 0);  
INSERT INTO `tbl_topic` VALUES (3, 1, ‘Announcements’, ‘This is 3nd Topic’, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:14’, 127, 1, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:14’, 127, ‘active’, 0, 0, 0);  
INSERT INTO `tbl_topic` VALUES (4, 1, ‘Announcements’, ‘This is 4nd Topic’, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:17’, 127, 1, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:17’, 127, ‘active’, 0, 0, 0);  
INSERT INTO `tbl_topic` VALUES (5, 1, ‘Announcements’, ‘topic 5’, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:22’, 127, 1, 1, ‘Admin’, ‘admin’, ‘2008-02-15 06:35:22’, 127, ‘active’, 0, 0, 0);

Query i am running

SELECT topicId, topicName, creatorId, createdBy, creatorClass, UNIX\_TIMESTAMP( createdAt ) AS createdAt, creatorIP, totalPosts, lastPosterId, lastPoster, lastPosterClass, UNIX\_TIMESTAMP( lastPostTime ) AS lastPostTime, lastPosterIP, is\_pinned  
FROM PUBLICFORUM.tbl\_topic  
FORCE INDEX ( is\_pinned )  
WHERE topicStatus = ‘active’  
AND is\_deleted =0  
AND (  
accessLevel \<=0  
)  
ORDER BY is\_pinned, lastPostTime

and when i explains this query… ( it shows filesort

tried repairing, optimizing… the table but no luck.

the other frequently varient of the query i will be using is  
SELECT topicId, topicName, creatorId, createdBy, creatorClass, UNIX\_TIMESTAMP(createdAt) as createdAt, creatorIP, totalPosts, lastPosterId, lastPoster, lastPosterClass, UNIX\_TIMESTAMP(lastPostTime) as lastPostTime, lastPosterIP, is\_pinned FROM PUBLICFORUM.tbl\_topic FORCE INDEX (is\_pinned) WHERE topicStatus = ‘active’ and forumId = 1 and is\_deleted = 0 and ( accessLevel\<=0 OR ( accessLevel=1 and topicId in (1, 2) ) ) ORDER BY is\_pinned, lastPostTime

and in the 2nd query the list of topicId in the in expression at end can increase many folds

any help would be great.