Nested query efficiency in MySQL I have a table with several attributes like this:

id  id2  attribute
--------------------------------
 1  100  blue 
 2  100  red
 3  100  green
 4  100  white
 5  102  blue
 6  102  green
 7  102  red
 8  103  red
 9  103  blue
10  103  white
11  104  red
12  104  black
13  104  green
14  104  orange
15  105  red
16  105  blue
17  105  green


I want to know:
- what are the top attributes for the entries that have 'blue'?
or
- what are the top attributes for the entries that have 'blue' and 'red'?

For the second query the results should be:

attribute   count1
--------------------
green       3
white       2


I can build the query dynamically. I have this working, using this approach:

SELECT
  mytable.attribute,
  count(mytable.id) as count1
FROM
    mytable,
    (SELECT
     id2
    FROM
     mytable
    WHERE
     attribute in ('blue', 'red')
    GROUP BY
     id2
    HAVING
     count(distinct attribute) = 2) as t
WHERE
 mytable.id2 = t.id2
and
 attribute NOT IN ('blue', 'red')
GROUP BY
 mytable.attribute
ORDER BY
 count1 desc


The problem is that if the inner query has a lot of entries the while process takes too long. Can anyone suggest a way to improve this?
Add indexes on (attribute, id2) and on (id2, attribute) and try this:

SELECT
    m.atttribute,
    COUNT(*) AS cnt
FROM 
    mytable AS m
  JOIN mytable AS m1
    ON  m1.attribute = 'blue'
    AND m1.id2 = m.id2
  JOIN mytable AS m2
    ON  m2.attribute = 'red'
    AND m2.id2 = m.id2
GROUP BY
    m.attribute
HAVING
    m.attribute NOT IN ('blue', 'red') 
ORDER BY
    cnt DESC ;


Test in SQL-Fiddle