Re: 'not in' query is very slow how to change it?

[Rick James]

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)