I have a table: CREATE TABLE `single_table` ( `forum_id` int(10) unsigned NOT NULL default '0', `id` int(10) unsigned NOT NULL auto_increment, `datestamp` datetime NOT NULL default '0000-00-00 00:00:00', `thread` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `author` varchar(37) NOT NULL default '', `subject` varchar(255) NOT NULL default '', `email` varchar(200) NOT NULL default '', `attachment` varchar(64) NOT NULL default '', `host` varchar(50) NOT NULL default '', `email_reply` char(1) NOT NULL default 'N', `approved` char(1) NOT NULL default 'N', `msgid` varchar(100) NOT NULL default '', `viewcount` int(11) NOT NULL default '0', `modifystamp` int(10) unsigned NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', `body` text NOT NULL, PRIMARY KEY (`id`), KEY `forum_id_3` (`forum_id`,`thread`,`modifystamp`,`approved`), KEY `approved` (`approved`,`datestamp`), KEY `parent` (`parent`), FULLTEXT KEY `body` (`body`,`author`,`subject`) ) TYPE=MyISAM
If I run a query like: select id from single_table where forum_id=5818 and parent=0 order by modifystamp desc It runs really slow (a minute) and explain says it is using filesort. However this query: select id from single_table where forum_id=5818 and parent=0 order by thread desc runs in .2 seconds and does not use a filesort. Is MySQL using the first part of forum_id_3 key for this query? Do I need to create a forum_id, parent, modifystamp key? This table has like 1.13 mill rows, so experimenting takes for ever. That is normally what I would. Just try stuff. Brian. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
