Query keeps on executing

Question

I'm executing below query, but it keeps on executing.

In place of some time I have a time value.

select distinct(col1) 
from table1
where col2 > 'some time'
  and col1 not in 
      (select distinct(col1) 
       from table2 
       where col2 not like '%XYZ%');

Answer 1

Apart from the fact that where not in () is almost always very slow, distinct will also be slow.

Try this, and let me know if it is better:

select distinct(col1) 
from table1
    left join table2 on table1.col1 = table2.col1
        and table2.col2 not like '%XYZ%'
where table1.col2 > 'some -time'
  and table2.col1 is null;

Note: You have to bear in mind that there could be a magnitude of other issues affecting the performance, such as hardware, indexing, other users etc.; the above query represents some improvement, but is not guaranteed to run blindingly quick.

Answer 2

First, a rant. DISTINCT is not a function. It'a a modifier than can appear in various places in SQL code, usually after SELECT or UNION or inside aggregate finctions, like COUNT(DISTINCT .. ) and (surprise) modifies their behaviour. For example SELECT has 2 possible modifiers, you can eitehr write SELECT ALL or SELECT DISTINCT and that affects if the query will return all rows or will remove duplicate rows. There is no reason to write DISTINCT(col1), it's the same as DISTINCT col1. Even if you write:

SELECT DISTINCT(col1), col2, col3, ...

it's exactly the same as (and that is how it's executed by the engine):

SELECT DISTINCT
    col1, col2, col3, ...

Anyway, lets go to the query. The first part:

SELECT DISTINCT col1 
FROM table1
WHERE col2 > 'some time'

is not at all easy to be optimized. You probably have or may have a few thousand (or million) different values of col2 that match the condition and for every one of them, there may be tens or thousands (or more!) distinct values of col1. So, even if an index on (col2, col1) is used, the execution has to do a lot of sorting (for every different col2 value, it would find a (sorted) list of col1 values and then try to sort these (millions) lists. Not very efficient.)

One thing you can do is identify that SELECT DISTINCT col1 is the same as SELECT col1 ... GROUP BY col1 and change the code to:

SELECT col1 
FROM table1
WHERE col2 > 'some time'
GROUP BY col1

so you can apply the trick/transformation:

SELECT col1 
FROM table1
GROUP BY col1
HAVING MAX(col2) > 'some time'

This can use a different index, on (col1, col2), and much more efficiently that before because no sorting is needed.

Then we have the second part, the subquery with table2. I prefer NOT EXISTS over NOT IN because it avoids the trap of NULL values (that gives unexpected results with NOT IN), so I'll rewrite the query as:

SELECT col1 
FROM table1 AS a
GROUP BY col1
HAVING MAX(col2) > 'some time'
   AND NOT EXISTS 
       ( SELECT 1 
         FROM table2 AS b 
         WHERE b.col1 = a.col1
           AND b.col2 NOT LIKE '%XYZ%'
       ) ;

An index on table2 (col1, col2) would help this, too, but the NOT LIKE '%...' construct suggests it cannot be very fast. You can also try this variation, depending on the data distribution, it may be more efficient than the previous:

SELECT col1 
FROM table1 AS a
WHERE NOT EXISTS 
       ( SELECT 1 
         FROM table2 AS b 
         WHERE b.col1 = a.col1
           AND b.col2 NOT LIKE '%XYZ%'
       ) 
GROUP BY col1
HAVING MAX(col2) > 'some time' ;