Stuck with optimization on MySql, getting using temporary, using file sort... messages on EXPLAIN

I'm profiling a web application, trying to cut on unnecessary delays on queries and I found one that seems to be simple, but take a lot of time for execute.

Using EXPLAIN I get the following messages:

Using where; Using temporary; Using filesort

This is the query:

SELECT `bt`.`id_brand`
FROM `brands_translation` AS `bt`
WHERE bt.code_language = 'es'
GROUP BY `bt`.`id_brand`
ORDER BY `bt`.`name` ASC

And the table definition:

CREATE TABLE IF NOT EXISTS `brands_translation` (
  `id_brand` int(64) unsigned NOT NULL,
  `code_language` varchar(3) NOT NULL,
  `name` varchar(128) NOT NULL,
  `link` varchar(255) default NULL,
  `logo` varchar(255) default NULL,
  `description` text NOT NULL,
  KEY `id_brand` (`id_brand`),
  KEY `code_language` (`code_language`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I try to solve it creating indexes for 开发者_高级运维every involved field with no result.

Any idea with that?

Thanks in advance

With a proper INDEX, MySQL may very well sort your results properly without the ORDER BY statement. You'll want to look into ORDER BY Optimization and LIMIT Optimization.

Otherwise, temporary and filesort usage come from a differing GROUP BY and ORDER BY. For this particular query, I would remove the indices you have in place and add this one:

ALTER TABLE  `brands_translation` ADD INDEX (`code_language` , `id_brand` , `name` );

Of course, this may affect other queries throughout your project. Once that's done, change your query to:

SELECT `bt`.`id_brand`
FROM `brands_translation` AS `bt`
WHERE bt.code_language = 'es'
GROUP BY `bt`.`id_brand`, `bt`.`name`
ORDER BY `bt`.`id_brand`, `bt`.`name`

Realizing that you may not want to group by name, you can remove name from the GROUP BY statement, but that will give you using temporary again (since the GROUP and ORDER are different).

please look at the following...

drop table if exists brands_translation;
create table brands_translation
(
code_language varchar(3) not null,
id_brand int unsigned not null,
-- other fields here...
primary key(code_language, id_brand) -- clustered composite primary key (please !!)
) 
engine=innodb;

why quote ` when you dont need to and sort out your data types pls.