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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
