MySQL Bugs: #30330: NOT IN faster than equals(=)

Bug #30330	NOT IN faster than equals(=)
Submitted:	9 Aug 2007 11:35	Modified:	27 Sep 2007 5:20
Reporter:	Naveen JP	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	4.1.12-log	OS:	Linux (Gentoo)
Assigned to:		CPU Architecture:	Any

Description:
I see that a statement of type `Type NOT IN( 'OVW','PRVW')' is much 
faster(0.5 sec) than `im.Type = 'BISTR'' (1.5 sec).

Given below is the complete query

  select count(*)
  FROM
  applicant a,
  interview_setup ist,
  companyinterview ci,
  interviewmodel im
  WHERE
   a.modifiedTime > '2007-06-00' AND
   a.id = ist.intervieweeId AND
   ist.companyInterviewId = ci.id AND
   ci.InterviewModelId = im.Id AND
--I run the query by uncommenting one of the following lines.
--im.Type = 'BISTR' 
--im.Type NOT IN( 'OVW','PRVW') 
   ;

Given below is the Explain query which does explain why this is happening but I 
thought it is still strange that it works this way

+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                    | key                       | key_len | ref                       | rows   | Extra       |
+----+-------------+-------+--------+----------------------------------+---------------------------+---------+------------------------------------+-------------+

when im.Type NOT IN( 'OVW','PRVW') is used ->
|  1 | SIMPLE      | im    | eq_ref | PRIMARY                          | PRIMARY                   |       4 | db.ci.InterviewModelId    |      1 | Using where |
+----+-------------+-------+--------+----------------------------------+---------------------------+---------+---------------------------+--------+-------------+

when im.Type = 'BISTR' is used ->
|  1 | SIMPLE      | im    | ref    | PRIMARY,idx_interviewmodel_type  | idx_interviewmodel_type   |      15 | const                     |   36   | Using where; Using index |

How to repeat:
Run query of above type by commenting the required statement as mentioned above.

Thank you for a problem report. Please, send complete explain results and SHOW CREATE TABLE results for all the tables involved.

More details including `show create table'

Attachment: FasterNotIn.sql (application/octet-stream, text), 10.71 KiB.

I found something that kind of "fixed" the problem. When I move the 
related conditions into a left outer join, the `NOT IN' or `equals(=)' did not 
vary in behavior. Given below is the change - 

  select count(*)
  FROM
  applicant a,
  interview_setup ist
  left outer join companyinterview ci on ist.companyInterviewId = ci.id 
  left outer join interviewmodel im on ci.InterviewModelId = im.Id 
  WHERE
   a.modifiedTime > '2007-06-00' AND
   a.id = ist.intervieweeId AND
im.Type = 'BISTR' 
--im.Type NOT IN( 'OVW','PRVW') 
   ;

Explain query does explain why, as the plans are same in both situations (with 
or without `NOT IN') -
+----+-------------+-------+--------+---------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                                           | key                               | key_len | ref                               | rows   | Extra       |
+----+-------------+-------+--------+---------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY                                                 | NULL                              |    NULL | NULL                              | 305619 | Using where |
|  1 | SIMPLE      | ist   | ref    | unq_interview_setup_1,idx_interview_setup_intervieweeid | idx_interview_setup_intervieweeid |       4 | talentplus.a.Id                   |      1 |             |
|  1 | SIMPLE      | ci    | eq_ref | PRIMARY                                                 | PRIMARY                           |       4 | talentplus.ist.CompanyInterviewId |      1 |             |
|  1 | SIMPLE      | im    | eq_ref | PRIMARY,idx_interviewmodel_type                         | PRIMARY                           |       4 | talentplus.ci.InterviewModelId    |      1 | Using where |
+----+-------------+-------+--------+---------------------------------------------------------+-----------------------------------+---------+-----------------------------------+--------+-------------+

Please, try to repeat with a newer version, 4.1.23, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".