title: Performace Lag in Mysql query
body: <p>This mysql query takes 5 minutes 20 seconds to execute</p>
  
  <pre><code>SELECT PROVIDER, COUNT(DISTINCT(NAME)) FROM   Test  WHERE NAME NOT IN (SELECT NAME 
  FROM Test   WHERE OPERATION = 'SIGN_IN' and Test.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) *    1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000) 
  AND Test.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 AND OPERATION='VALIDATE'  GROUP BY PROVIDER;
  </code></pre>
  
  <p>The explain give the following result</p>
  
  <pre><code>explain SELECT  PROVIDER, COUNT(DISTINCT(NAME)) FROM   Test  WHERE NAME NOT IN  
  (SELECT NAME  FROM Test   WHERE OPERATION = 'SIGN_IN' and Test.CREATED_TIME BETWEEN 
  UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000) AND         Test.CREATED_TIME    BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 AND OPERATION='VALIDATE'  GROUP BY PROVIDER;
  +----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+
  | id | select_type        | table           | type           | possible_keys                                                        | key                     | key_len | ref  | rows   | Extra                              |
  +----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+
  |  1 | PRIMARY            | Test | ALL            | Test_CTndx,Test_CORndx                         | NULL                    | NULL    | NULL | 137523 | Using where; Using filesort        |
  |  2 | DEPENDENT SUBQUERY | Test | index_subquery | Test_NAMEndx,Test_CTndx,Test_CORndx | Test_NAMEndx | 303     | func |    148 | Using where; Full scan on NULL key |
  +----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+
  </code></pre>
  
  <p>Number of rows in a table is 50,000. </p>
  
  <p>How can I optimize this ? </p>