Ok, so, I created that index. Now, it does not use filesort. Still 3 seconds, but better than a minute.
Now, however, the previously fast query is using filesort. WTF? I will be needing both of these queries in Phorum 5 so I need to have them both running as fast as possible. I will add a forum_id, parent, thread key and see what happens. Brian. ----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Brian Moon" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 22, 2002 12:28 AM Subject: Re: Getting rid of filesort | 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 | | --------------------------------------------------------------------- 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
