title: MySQL - Find rows matching all rows from joined table AND string from other tables
body: <p>this is a follow up from <a href="https://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table">MySQL - Find rows matching all rows from joined table</a></p>
  
  <p>Thanks to this site the query runs perfectly.</p>
  
  <p>But now i had to extend the query for a search for artist and track. This has lead me to the following query:</p>
  
  <pre><code>SELECT DISTINCT`t`.`id` 
  FROM `trackwords` AS `tw`  
  INNER JOIN `wordlist` AS `wl` ON wl.id=tw.wordid  
  INNER JOIN `track` AS `t` ON tw.trackid=t.id  
  WHERE (wl.trackusecount&gt;0) AND  
  (wl.word IN ('please','dont','leave','me')) AND  
  t.artist IN ( 
   SELECT a.id  
   FROM artist as a 
   INNER JOIN `artistalias` AS `aa` ON aa.ref=a.id  
   WHERE a.name LIKE 'pink%' OR  aa.name LIKE 'pink%' 
  )  
  GROUP BY tw.trackid 
  HAVING (COUNT(*) = 4);
  </code></pre>
  
  <p>The Explain for this query looks quite good i think:</p>
  
  <pre><code>+----+--------------------+-------+--------+----------------------------+---------+---------+-----------------+------+----------------------------------------------+
  | id | select_type        | table | type   | possible_keys              | key     | key_len | ref             | rows | Extra                                        |
  +----+--------------------+-------+--------+----------------------------+---------+---------+-----------------+------+----------------------------------------------+
  |  1 | PRIMARY            | wl    | range  | PRIMARY,word,trackusecount | word    | 767     | NULL            |    4 | Using where; Using temporary; Using filesort | 
  |  1 | PRIMARY            | tw    | ref    | wordid,trackid             | wordid  | 4       | mbdb.wl.id      |   31 |                                              | 
  |  1 | PRIMARY            | t     | eq_ref | PRIMARY                    | PRIMARY | 4       | mbdb.tw.trackid |    1 | Using where                                  | 
  |  2 | DEPENDENT SUBQUERY | aa    | ref    | ref,name                   | ref     | 4       | func            |    2 |                                              | 
  |  2 | DEPENDENT SUBQUERY | a     | eq_ref | PRIMARY,name,namefull      | PRIMARY | 4       | func            |    1 | Using where                                  | 
  +----+--------------------+-------+--------+----------------------------+---------+---------+-----------------+------+----------------------------------------------+
  </code></pre>
  
  <p>Did you see room for optimization ? Query has a runtime from around 7secs, which is to much unfortunatly. Any suggestions are welcome.</p>
  
  <p>TIA</p>