On Wed, Aug 21, 2002 at 11:57:53PM -0500, Brian Moon wrote: > 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?
That should do the trick, yes. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 15 days, processed 323,657,916 queries (235/sec. avg) --------------------------------------------------------------------- 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
