Please Help Me With Mysql Slow Query Analysis

Question

I have this mysql query that I am trying to analyze. It is very slow, the visitor table here is about 50K entries, this query never returns. When I tried an explain statement, I found out that the index is not being used on the visitor table, In spite of the index being available. Now this is the great puzzle I need help solving. Any hints appreciated.

Query:

select distinct
  visitor0_.ID as ID130_,      

  case when visitor0_1_.id is not null then 1 when
  visitor0_.ID is not null then 0
  end as clazz_

from Visitor visitor0_ 
left outer join Operator visitor0_1_ on visitor0_.ID=visitor0_1_.id
where (visitor0_.ID not in
    (select operator1_.id 
     from Operator operator1_ 
     inner join Visitor operator1_1_ on operator1_.id=operator1_1_.ID))
  and (exists 
    (select visitorpro2_.ID 
     from VisitorProfileField visitorpro2_, ProfileField profilefie3_ 
     where visitorpro2_.profileFieldID=profilefie3_.ID 
       and visitorpro2_.visitorID=visitor0_.ID 
       and profilefie3_.name='subscription86' 
       and visitorpro2_.numberVal=1 
       and visitorpro2_.stringVal='Manual'))

Explain output screen shot: http://grab.by/grabs/9c3a629a25fc4e9ec0fa54355d4a092c.png

Answer 1

From what I infer of your query, the following should produce the same result, with no subqueries and a lot faster performance.

select v.ID as ID130_, 0 as clazz_
from Visitor v
left outer join (VisitorProfileField vpf join ProfileField pf 
                   on vpf.profileFieldID = pf.ID)
  on v.ID = vpf.visitorID and pf.name='subscription86' 
    and vpf.numberVal=1 and vpf.stringVal='Manual'
left outer join Operator o on v.ID = o.ID
where o.ID IS NULL;

Please explain if I got some of it wrong. It appears that your NOT IN predicate excludes any Visitor id's that match any id's in Operator. That is, the subquery generates a list of all id's that are in both tables, so the NOT IN condition is equivalent to an outer join to Operator and a simple test where o.ID IS NULL.

This means the CASE expression in your select-list is meaningless, since it will certainly be 0 if your conditions match only Visitor rows that don't match any rows in Operator.

I think something is seriously confused in your query.

Also, it appears that you're using the EAV antipattern in the VisitorProfileField and ProfileField tables. This is going to cause you a lot of trouble.