MySQL Forums
Forum List  »  Optimizer & Parser

Re: 'not in' query is very slow how to change it?
Posted by: Rick James
Date: November 18, 2010 12:59AM

It is usually best to turn subqueries into JOINs.

select  `question`
    from  questions
    where  `iid` = '2'
      and  `id` >= '14'
      and  `question` not in (
        SELECT  q
            from  questions,occur
            where  q = question
              and  occurid = 'i25085126'
              and  occur.a <> ''
            group by  q
            order by  occur.id desc)   -- why have an order by?
      and  `active` = 1
    order by  ord asc
    limit  0, 1;
That does not make sense. The subquery demands that `questions` have a row with the given `question` (= q), but then the outer SELECT does not want it to exist.

Anyway, here is an attempt to rearrange the query:
select  q.`question`
    from  questions q
    LEFT JOIN  ( SELECT DISTINCT o.q
            FROM questions q2
            JOIN occur o ON  q2.question = o.q  -- is `q` in `occur`?
            where  o.occurid = 'i25085126'
              and  o.a <> ''
              ) x
    where  q.`iid` = '2'
      and  q.`id` >= '14'
      AND  x.question  IS NULL    -- the counterpart to NOT IN
      and  q.`active` = 1
    order by  q.ord asc
    limit  0, 1;
q2 can possibly be eliminated.

Does occur have
INDEX(occurid) (or PRIMARY KEY)
Does questions have
INDEX(question)
INDEX(iid, active, id)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 'not in' query is very slow how to change it?
3539
November 18, 2010 12:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.