Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

its about the following query.

EXPLAIN SELECT phrase, infinitiv 
FROM `dict_verbs_all` `t` 
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN `dict_verbs_german` `c` 
ON (`c`.`id`=`verbsGerman_c`.`verb_language_id`)
WHERE (t.phrase LIKE 'spie%' AND t.language=1) 
GROUP BY infinitiv 
LIMIT 5

It uses all the time filesort

Using where; Using temporary; Using filesort

Here are my columns

CREATE TABLE `dict_verbs_all` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phrase` varchar(30) NOT NULL,
  `language` smallint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uPhraseLanguage` (`phrase`,`language`)
) ENGINE=MyISAM AUTO_INCREMENT=337740 DEFAULT CHARSET=utf8;


CREATE TABLE `dict_verbs_all_language` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `verb_id` int(11) DEFAULT NULL,
  `verb_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `U_verb_conjugation` (`verb_id`,`verb_language_id`),
  KEY `verb_id` (`verb_id`),
  KEY `conjugation_id` (`verb_language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dict_verbs_german` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infinitiv` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uInfinitiv` (`infinitiv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have an index on all relevant columns but its not beeing used and I don't know why. I hope you can help me how to eliminate using temporary and using filesort

share|improve this question
1  
Thanks for all of the information. Could you help us understand your question a bit better? Thanks. –  zlatan Jan 10 '13 at 20:58
    
The LIMIT 5 means it will just return 5 rows max. Is it still slow? –  ypercube Jan 10 '13 at 20:59
    
Hi swasheck,thank you for your fast reply. –  toni Jan 10 '13 at 21:19
    
the problem is that it uses Using temporary; Using filesort instead of the index –  toni Jan 10 '13 at 21:20
add comment

1 Answer

Probably because the table "dict_verbs_german" is the last table joined.

When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions.

  • dict_verbs_german is the first non-constant table in the JOIN order, and
  • dict_verbs_german has an index that marches the ORDER BY column list.

To avoid "using temporary", you'd have to meet all these conditions.

  • dict_verbs_german is the first table in the JOIN order, and
  • dict_verbs_german has an index that marches the ORDER BY column list.

Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too.


MySQL documentation

In some cases, MySQL cannot use indexes to resolve the ORDER BY . . .. These cases include . . . joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

MySQL documentation for optimizing limit queries

(I don't think this last reference will help you with this problem, but it's good to know where to find it.)


Later . . .

Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.