Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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%');
share|improve this question
    
Show us the 2 CREATE TABLE statements and the EXPLAIN plan. –  ypercube Jun 7 '13 at 13:42
    
you mean the structure of both table ? –  user21546 Jun 7 '13 at 13:49
    
Yes, the output of SHOW CREATE TABLE table1; (and same for table2) –  ypercube Jun 7 '13 at 13:52
add comment

2 Answers

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.

share|improve this answer
1  
not in () is not always very slow. I prefer NOT EXISTS but when columns are not nullable they usually have same performance - and not bad with proper indexing. –  ypercube Jun 7 '13 at 13:12
    
Agree... that's why I said "almost always". It certainly has its place, but is often used far more than it should be. –  RoKa Jun 7 '13 at 13:14
    
its still taking time...let me mention that there are lakh of records in this table. –  user21546 Jun 7 '13 at 13:37
    
How long does it run as compared to before? –  RoKa Jun 7 '13 at 15:16
    
it keeps on executing !! no output .. –  user21546 Jun 8 '13 at 13:53
show 3 more comments

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' ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.