Performace call in Mysql query

Question

This mysql query takes 5 minutes 20 seconds to execute

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; 

The explanation gives the following result:

 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 | +----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+ 

The number of rows in the table is 50,000.

How can I optimize this?

Answer 1

Re-writing your query to replace NOT IN equivalent NOT EXISTS statement should be faster.

For example, try something like this:

 SELECT t1.PROVIDER, COUNT(DISTINCT(t1.NAME)) FROM Test t1 WHERE t1.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE() - INTERVAL 1 DAY) * 1000 AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 AND t1.OPERATION='VALIDATE' and NOT EXISTS ( select null from test t2 where t2.OPERATION = 'SIGN_IN' and t2.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE() - INTERVAL 1 DAY) * 1000 AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 and t2.name = t1.name ) GROUP BY t1.PROVIDER;