Announcement

Announcement Module
Collapse
No announcement yet.

trying to get rid of filesort

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • trying to get rid of filesort

    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.
Working...
X