Optimize Query with huge NOT IN Statement

mysql, query-optimization

Solution

The subquery doesn't need the DISTINCT, and the WHERE clause on the outer query is not needed either, since you are already filtering by the NOT IN.

Try:

select distinct sourcesite
from contentmeta
where sourcesite not in (
    select sourcesite
    from contentmeta
    where timestamp > '2011-03-15'
);

Problem

I am trying to find the sourcesites that ONLY exist before a certain timestamp. This query seems very poor for the job. Any idea how to optimize or an index that might improve? ``` select distinct sourcesite from contentmeta where timestamp <= '2011-03-15' and sourcesite not in ( select distinct sourcesite from contentmeta where timestamp>'2011-03-15' ); ``` There is an index on sourcesite and timestamp, but query still takes a long time ``` mysql> EXPLAIN select distinct sourcesite from contentmeta where timestamp <= '2011-03-15' and sourcesite not in (select distinct sourcesite from contentmeta where timestamp>'2011-03-15'); +----+--------------------+-------------+----------------+---------------+----------+---------+------+--------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------------+----------------+---------------+----------+---------+------+--------+-------------------------------------------------+ | 1 | PRIMARY | contentmeta | index | NULL | sitetime | 14 | NULL | 725697 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | contentmeta | index_subquery | sitetime | sitetime | 5 | func | 48 | Using index; Using where; Full scan on NULL key | +----+--------------------+-------------+----------------+---------------+----------+---------+------+--------+-------------------------------------------------+ ```

Original source