title: Optimize mysql NOT IN query by using temporary variable
body: <p>I was trying to optimize NOT IN clause in mysql: Some how I ended up in the following query:</p>
  
  <pre><code>SELECT @i:=(SELECT correct_option_word_id FROM sent_question WHERE msisdn='abc');
  SELECT * FROM word WHERE  @i IS NULL OR word_id NOT IN (@i);
  </code></pre>
  
  <p>There is no relationship between <code>sent_question</code> table and <code>word</code> table. And also I cannot place index on <code>correct_option_word_id</code>.</p>
  
  <p>Can somebody please explain, will this method even optimize the query or not?</p>
  
  <p><strong>UPDATE</strong>: As mentioned <a href="http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/" rel="nofollow">here</a> that both the methods: NOT IN and LEFT JOIN/IS NULL are almost equally efficient. That's why I don't want to use LEFT JOIN/IS NULL method.</p>
  
  <p><strong>UPDATE 2</strong>:
  Explain results for <strong>original query</strong>:</p>
  
  <pre><code>EXPLAIN SELECT * FROM word WHERE word_id NOT IN (SELECT correct_option_word_id FROM sent_question WHERE msisdn='abc');
  +----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
  | id | select_type        | table         | type | possible_keys           | key                     | key_len | ref   | rows | Extra       |
  +----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
  |  1 | PRIMARY            | word          | ALL  | NULL                    | NULL                    | NULL    | NULL  |   10 | Using where |
  |  2 | DEPENDENT SUBQUERY | sent_question | ref  | fk_question_subscriber1 | fk_question_subscriber1 | 48      | const |    1 | Using where |
  +----+--------------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------------+
  </code></pre>