Slow Query

player_to_team_history local

Table: player_to_team_histories
Create Table: CREATE TABLE `player_to_team_histories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player_id` int(11) DEFAULT NULL,
  `team_history_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `weight` int(11) DEFAULT NULL,
  `height_feet` int(11) DEFAULT NULL,
  `height_inches` int(11) DEFAULT NULL,
  `height_in_inches` int(11) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `bio_link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `swings` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `throws` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `year` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `non_player` tinyint(1) DEFAULT NULL,
  `redshirt` tinyint(1) DEFAULT NULL,
  `previous_school_text` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `player_dict` text COLLATE utf8_unicode_ci,
  `not_valid` tinyint(1) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_player_to_team_histories_on_player_id` (`player_id`),
  KEY `index_player_to_team_histories_on_team_history_id` (`team_history_id`),
  KEY `index_player_to_team_histories_on_height_in_inches` (`height_in_inches`),
  KEY `index_player_to_team_histories_on_weight` (`weight`),
  KEY `index_player_to_team_histories_on_ht_wt` (`weight`,`height_in_inches`),
  KEY `index_player_to_team_histories_on_slug` (`slug`),
  KEY `index_pth_valid_team_history` (`not_valid`,`team_history_id`)
) ENGINE=InnoDB AUTO_INCREMENT=599872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

players local

       Table: players
Create Table: CREATE TABLE `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `IsActive` tinyint(1) DEFAULT NULL,
  `IsVisible` tinyint(1) DEFAULT NULL,
  `FirstName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LastName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HeightFeet` int(11) DEFAULT NULL,
  `HeightInches` int(11) DEFAULT NULL,
  `Weight` int(11) DEFAULT NULL,
  `Birthday` date DEFAULT NULL,
  `Gender` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HometownCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HometownState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HometownZip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HometownCountry` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HighSchoolId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HighSchoolIdTemp` int(11) DEFAULT NULL,
  `HighSchoolGradYear` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CollegeYear` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Redshirted` tinyint(1) DEFAULT NULL,
  `Transferred` tinyint(1) DEFAULT NULL,
  `CollegeId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CollegeIdTemp` int(11) DEFAULT NULL,
  `CollegeGradYear` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `OtherAccountId` int(11) DEFAULT NULL,
  `PreviousCollegeId` int(11) DEFAULT NULL,
  `CurrentTeamId` int(11) DEFAULT NULL,
  `LateralRecommendationReason` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LateralRecommendationLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CreationDate` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CreatedBy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LastModifiedDate` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LastModifiedBy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TwitterLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FacebookLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PersonalWebsite` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PlayerImage` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FirstNameNickName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `NeulionID` int(11) DEFAULT NULL,
  `OtherTeamID` int(11) DEFAULT NULL,
  `OtherSportTypeID` int(11) DEFAULT NULL,
  `SourceDataTypeID` int(11) DEFAULT NULL,
  `PlayerTypeID` int(11) DEFAULT NULL,
  `LoadID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SameNameTeammate` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SameNameSchoolMate` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SD_SportID` int(11) DEFAULT NULL,
  `SD_PlayerID` int(11) DEFAULT NULL,
  `ZeroNCAAStats` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ModifiedByPythonGame` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Missing2011` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Transfer2011` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `RecruitingClass` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `high_school_id` int(11) DEFAULT NULL,
  `high_school_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `not_valid` tinyint(4) DEFAULT NULL,
  `city_to_zip_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_players_on_FirstName` (`FirstName`),
  KEY `index_players_on_LastName` (`LastName`),
  KEY `index_players_on_user_id` (`user_id`),
  KEY `index_players_on_hometown_state` (`HometownState`)
) ENGINE=InnoDB AUTO_INCREMENT=479775 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

query

explain SELECT 
    COUNT(DISTINCT players.HometownState)
FROM
    player_to_team_histories
        INNER JOIN
    team_histories ON team_histories.id = player_to_team_histories.team_history_id
        INNER JOIN
    teams ON teams.id = team_histories.team_id
        INNER JOIN
    accounts ON accounts.id = teams.account_id
        INNER JOIN
    players ON players.id = player_to_team_histories.player_id
WHERE
    player_to_team_histories.not_valid IS NULL AND player_to_team_histories.not_valid IS NULL AND (accounts.AccountTypeId = 2) AND (team_histories.academic_year = '2012') AND (teams.sport_type_id = 5) AND (players.HometownState IN ('AL','AK',
        'AZ',
        'AR',
        'CA',
        'CO',
        'CT',
        'DE',
        'FL',
        'GA',
        'HI',
        'ID',
        'IL',
        'IN',
        'IA',
        'KS',
        'KY',
        'LA',
        'ME',
        'MD',
        'MA',
        'MI',
        'MN',
        'MS',
        'MO',
        'MT',
        'NE',
        'NV',
        'NH',
        'NJ',
        'NM',
        'NY',
        'NC',
        'ND',
        'OH',
        'OK',
        'OR',
        'PA',
        'RI',
        'SC',
        'SD',
        'TN',
        'TX',
        'UT',
        'VT',
        'VA',
        'WA',
        'WV',
        'WI',
        'WY'))

team_histories local

Table: team_histories
Create Table: CREATE TABLE `team_histories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) DEFAULT NULL,
  `academic_year` varchar(255) DEFAULT NULL,
  `child_conference_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `roster_link` varchar(255) DEFAULT NULL,
  `schedule_link` varchar(255) DEFAULT NULL,
  `team_page_link` varchar(255) DEFAULT NULL,
  `head_coach` varchar(255) DEFAULT NULL,
  `national_ranking` int(11) DEFAULT NULL,
  `sagarin_ranking` int(11) DEFAULT NULL,
  `wins` int(11) DEFAULT NULL,
  `losses` int(11) DEFAULT NULL,
  `ties` int(11) DEFAULT NULL,
  `scraped_at` datetime DEFAULT NULL,
  `division_text` varchar(255) DEFAULT NULL,
  `conference_text` varchar(255) DEFAULT NULL,
  `school_link` varchar(255) DEFAULT NULL,
  `updated_at` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `roster_link_year` int(11) DEFAULT NULL,
  `classification_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `team_history_team_id_index` (`team_id`),
  KEY `team_history_academic_year` (`academic_year`),
  KEY `index_th_on_cshild_conference_id` (`child_conference_id`)
) ENGINE=MyISAM AUTO_INCREMENT=60740 DEFAULT CHARSET=latin1