Speed up DISTINCT query in MySQL

Question

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:

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

Here is an explain of the above query.

enter image description here

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.

If you have multiple questions, you should create multiple topics for them, as people will be more likely to help. — Igor, Sep 27 '11 at 20:58
You also might want to paste an explain plan for your query. It would help to see what MySQL's optimizer is doing. — Kris Robison, Sep 27 '11 at 21:05
How long it takes to run this query and how many rows it returns? `select name_id from v2_CA_venue_option_map where code = "a_event_types" group by name_id` — Karolis, Sep 27 '11 at 21:49
@RonSper Oh.. so it seems that you are right, your query can't perform much better. — Karolis, Sep 27 '11 at 22:24

score 0 · Answer 1 · answered Sep 27 '11 at 21:04

0

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

answered Sep 27 '11 at 21:04

Parris Varney

11,320
12
47
76

1

I was looking into that but articles such as [this one](http://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql) say that distinct is faster. – RonSper Sep 27 '11 at 21:06
Right, if you group over every column, try only grouping over name_id. How many results does this query give, btw? – Parris Varney Sep 27 '11 at 21:08
1

your query above executes around 0.5153 sec. this gives 19 results. – RonSper Sep 27 '11 at 21:15
@Ron: Read the comment on the answer in the question you link to. – ypercubeᵀᴹ Sep 27 '11 at 23:02
1

right, they are performance equivalent if indexed, and distinct is faster if there is not an index. – RonSper Sep 27 '11 at 23:14
DISTINCT would only be faster vs the same query grouping over all of the columns in the select clause. – Parris Varney Sep 28 '11 at 19:35

score 0 · Answer 2 · answered Sep 27 '11 at 22:59

0

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'
      )

answered Sep 27 '11 at 22:59

ypercubeᵀᴹ

113,259
19
174
235

1

thanks for the suggestion. 19 total, Query took 5.6770 sec, not even close to the performance of DISTINCT. – RonSper Sep 27 '11 at 23:03

Speed up DISTINCT query in MySQL

2 Answers2