Group By on column from joined table

Question

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

Answer 1

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.