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.