Speed up DISTINCT query in MySQL

I have a InnoDB MYSQL table that stores a multi select name ( or 'code' as the table calls it), a parent object's id (parent_id) and the name of the option selected in the multi select (name_id):

CREATE TABLE IF NOT EXISTS `v2_CA开发者_运维问答_venue_option_map` (
  `map_id` int(11) NOT NULL auto_increment,
  `code` varchar(30) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `name_id` int(11) NOT NULL,
  PRIMARY KEY  (`map_id`),
  UNIQUE KEY `3way_unique` (`code`,`parent_id`,`name_id`),
  KEY `name_id` (`name_id`),
  KEY `filter` (`code`,`name_id`),
  KEY `parent_id` (`parent_id`),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=875156 ;

and a simple table to store the names (i figured i would show this because its used in the query):

CREATE TABLE IF NOT EXISTS `v2_CA_venue_option_name` (
  `name_id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`name_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Venue Option Names' AUTO_INCREMENT=60 ;

That I would like to optimize for the following query:

SELECT DISTINCT name_id, name 
FROM `v2_CA_venue_option_map` 
JOIN `v2_CA_venue_option_name` 
    USING (name_id) 
WHERE code = "a_event_types"

This query takes about 600ms to execute and I was wondering:

1. If I could place an index on the table to speed up this distinct query.
2. How could I get the same result with better performance.

Here is an explain of the above query.

UPDATE, I removed my second question.

UPDATE, It seems that the best way to speed this up would be to store the output in a separate table once and make the calls to that table from then on, as this table just can't perform the query quick enough for my needs and indexes don't seem to help for this DISTINCT query.

Try a lazy group by instead of distinct, it's one less column mysql has to worry about.

SELECT name_id, name 
FROM `v2_CA_venue_option_map` 
JOIN `v2_CA_venue_option_name` 
    USING (name_id) 
WHERE code = "a_event_types"
GROUP BY name_id

You can try changing the query from a join to a semi-join, using EXISTS. No need for DISTINCT:

SELECT name_id
     , name 
FROM v2_CA_venue_option_name AS n
WHERE EXISTS
      ( SELECT *
        FROM v2_CA_venue_option_map AS m
        WHERE m.name_id = n.name_id 
          AND m.code = 'a_event_types'
      )