Why Does Adding a Fifth Argument in a MySQL "IN" Clause Dramatically Slow Things Down?

Question

I have three tables named Item, Event, and Seat, constructed like this:

Item
Id (int)
EventId (int)
Section (int)

Event
Id (int)
VenueId (int)
Configuration (int)
Home_team(int)

Seat
Id (int)
VenueId (int)
Configuration (int)
Section (int)

I am using the following query to select all items that are tied to particular home teams:

SELECT Item.Id
FROM Item
JOIN Event     
ON Event.Id = Item.EventId    
JOIN Seat USING (VenueId, Configuration)    
WHERE Seat.Section = Item.Section    
AND Event.Home_team IN (1,2,3,4)

This query runs very quickly--for reference, in about 0.6 sec. Yet when I add on additional argument to the IN clause in the last line:

AND Event.Home_team IN (1,2,3,4,5)

...it gets DRAMATICALLY slower--for reference, it takes about 24 sec.

I have tried changing the different arguments in the IN clause, and it doesn't matter what they are; things always run quickly with up to four, but once I add a fifth it gets much slower. the Event.Home_team field is indexed. I have also tried substituting the last line with this form:

AND (Event.Home_team = 1 OR Event.Home_team = 2 OR Event.Home_team = 3 OR Event.Home_team =4 OR Event.Home_team =5)

and I find that for this, also, things run quickly when there are four options but very slowly once I add a fifth.

This seems like bizarre behavior. Can anyone tell me why it would be occurring?

EDIT
Commenters have asked for my EXPLAIN statement. Here it is:

**id  select_type  table      type    possible_keys                                  keys        key_len   ref           rows     Extra**
  1   SIMPLE       seat       ref     seat_FI_1,seat_FI_2,section..                  exclude     4         const         17147
  1   SIMPLE       event      ref     PRIMARY,home_team,configuration_id, VenueId    VenueId     5         seat.VenueId  1        Using where
  1   SIMPLE       item       ref     FI_2,item_FI_3                                 FI_2        5         event.id      12       Using where

Also, I should note that there's an additional clause in the WHERE statement that I initially left out for simplicity:

Seat.exclude = 0

Exclude can be set to either 0 or 1. It is also indexed.

Answer 1

Try joining the Seat ID to the Item table by SectionID, and use WHERE clauses for the Venue and Configuration.

SELECT Item.Id
FROM Item
JOIN Event     
ON Event.Id = Item.EventId    
JOIN Seat 
ON Seat.Section = Item.Section   
WHERE Event.Configuration = Seat.Configuration
AND Event.Venue = Seat.Venue  
AND Event.Home_team IN (1,2,3,4,5)

Make sure you have indexes on all EventIDs and Section fields. Having indexes on the Section, Configuration, and Home_team fields wouldn't hurt either.

Answer 2

Perhaps that fifth value makes MySQL fetch a lot more rows (if your data distribution is heavily skewed towards that Home_team.) Or perhaps it's just a few more records but it's just enough for MySQL to decide to use another query plan, and that other query plan might be inefficient.

First of all, run ANALYZE TABLE (or OPTIMIZE TABLE) on all the tables involved, in case your index statistics are incorrect. Then compare the EXPLAIN of both queries, the fast one and the slow one.