title: MySQL: Optimizing a query with NOT EXISTS a subquery
body: <p>Let me just say, first of all, that I'm not a mySQL guru; while I use it adequately I don't know a lot of details about it.  In a system I just inherited, I've got this query:</p>
  
  <pre><code>SELECT DISTINCT profile2.f3
  FROM   node AS profile
         JOIN node AS profile2
           ON ( profile.f1 = profile2.f1 )
  WHERE  profile.f2 = "aString"
         AND profile.f3 = "anotherString"
         AND profile2.f2 = "aThirdString"
         AND NOT EXISTS (SELECT profile3.f1
                         FROM   node AS profile3
                         WHERE  profile3.f1 = profile.f1
                                AND profile3.f2 = "yetAnotherString") ;
  </code></pre>
  
  <p><code>SHOW CREATE TABLE</code> gives:</p>
  
  <pre><code>CREATE TABLE `node` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
    `f1` varchar(200) NOT NULL,
    `f2` varchar(200) NOT NULL,
    `f3` mediumtext NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `nodeindex` (`graph`(20),`f1`(100),`f2`(100),`f3`(100)),
    KEY `ix_node_f1` (`f1`),
    KEY `ix_node_graph` (`graph`),
    KEY `ix_node_f3` (`f3`(255)),
    KEY `ix_node_f2` (`f2`),
    KEY `node_po` (`f2`,`f3`(130)),
    KEY `node_so` (`f1`,`f3`(130)),
    KEY `node_sp` (`f1`,`f2`(130)),
    FULLTEXT KEY `node_search` (`f3`)
  ) ENGINE=MyISAM AUTO_INCREMENT=455854703 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p><code>EXPLAIN EXTENDED</code> gives:</p>
  
  <pre><code>+----+--------------------+----------+------+--------------------------------------------------------------------------------------+---------+---------+-----------------------------------+-------+----------+------------------------------+
  | id | select_type        | table    | type | possible_keys                                                                        | key     | key_len | ref                               | rows  | filtered | Extra                        |
  +----+--------------------+----------+------+--------------------------------------------------------------------------------------+---------+---------+-----------------------------------+-------+----------+------------------------------+
  |  1 | PRIMARY            | profile  | ref  | ix_node_f1,ix_node_f3,ix_node_f2,node_po,node_so,node_sp,node_search                 | node_po | 994     | const,const                       | 49084 |   100.00 | Using where; Using temporary |
  |  1 | PRIMARY            | profile2 | ref  | ix_node_f1,ix_node_f2,node_po,node_so,node_sp                                        | node_sp | 994     | sumazi_prdf.profile.f1,const      |     1 |   100.00 | Using where                  |
  |  2 | DEPENDENT SUBQUERY | profile3 | ref  | ix_node_f1,ix_node_f2,node_po,node_so,node_sp                                        | node_sp | 994     | sumazi_prdf.profile.f1,const      |     1 |   100.00 | Using where                  |
  +----+--------------------+----------+------+--------------------------------------------------------------------------------------+---------+---------+-----------------------------------+-------+----------+------------------------------+
  </code></pre>
  
  <p>As I say, I'm not an RDBMS guru, but my intuition suggests that the performance of this query could be substantially improved.  Any suggestions?</p>