Query optimization help needed

[Jesse Sheidlower]

I asked for help with a version of this query a few months
ago, and subsequently made some improvements to it, and also
fooled around with other parts of my app that were in need of
tuning. I've since done some more extensive benchmarking and
realized that this query really is somewhat slow. Even though
the data set is rather small and everything is (I think)
properly indexed and the joins are sensible, I can't seem to
get rid of the "using temporary" and "using filesort" in my
EXPLAIN. I'd be grateful for any suggestions for improving
this.

Here's the query (obviously I run it with different values for
"subject.name" and different "LIMIT" values, but this is
representative):

SELECT citation.*, DATE_FORMAT(citation.last_modified, '%e %M, %Y') AS last_modified 
FROM citation, subject, citation_subject
WHERE subject.name = 'History'
AND citation_subject.subject_id = subject.id
AND citation_subject.citation_id = citation.id
AND citation.deleted = 0
ORDER BY citation.stripped_word, FIELD(citation.part_of_speech, 'NOUN', 'ADJECTIVE',
'ADVERB', 'VERB'), citation.id 
LIMIT 150, 50

and EXPLAIN gives me this:

*************************** 1. row ***************************
        table: subject
         type: ref
possible_keys: PRIMARY,name
          key: name
      key_len: 50
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
        table: citation_subject
         type: ref
possible_keys: citation_id,subject_id
          key: subject_id
      key_len: 4
          ref: subject.id
         rows: 169
        Extra: Using index
*************************** 3. row ***************************
        table: citation
         type: eq_ref
possible_keys: PRIMARY,deleted
          key: PRIMARY
      key_len: 4
          ref: citation_subject.citation_id
         rows: 1
        Extra: Using where

Finally, here are the three tables involved. I've trimmed out the
irrelevant columns:

CREATE TABLE `citation` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(50) NOT NULL default '',
  `stripped_word` varchar(50) default NULL,
  `part_of_speech` enum('NOUN','VERB','ADJECTIVE','ADVERB') NOT NULL default 'NOUN',
  `last_modified` timestamp(14) NOT NULL,
  `deleted` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `deleted` (`deleted`),
  KEY `word` (`word`),
  KEY `stripped_word` (`stripped_word`)
) TYPE=MyISAM

CREATE TABLE `citation_subject` (
  `id` int(11) NOT NULL auto_increment,
  `citation_id` int(11) NOT NULL default '0',
  `subject_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `citation_id` (`citation_id`,`subject_id`),
  KEY `subject_id` (`subject_id`,`citation_id`)
) TYPE=MyISAM 

CREATE TABLE `subject` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `deleted` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`)
) TYPE=MyISAM 

Thank you for any suggestions.

Jesse Sheidlower