public
Last active — forked from /player_to_team_history local

This query runs 5x slower on my remote staging server than it does on my local dev machine

  • Download Gist
accounts local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
Table: accounts
Create Table: CREATE TABLE `accounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`AccountId` varchar(255) DEFAULT NULL,
`AccountIdTemp` varchar(255) DEFAULT NULL,
`HighSchoolTempId` varchar(255) DEFAULT NULL,
`IsActive` tinyint(1) DEFAULT NULL,
`IsVisible` tinyint(1) DEFAULT NULL,
`IsPaidAd` varchar(255) DEFAULT NULL,
`boolean` varchar(255) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`CommonName` varchar(255) DEFAULT NULL,
`ShortName` varchar(255) DEFAULT NULL,
`AbbreviatedName` varchar(255) DEFAULT NULL,
`OtherName` varchar(255) DEFAULT NULL,
`NickName` varchar(255) DEFAULT NULL,
`FemaleNickName` varchar(255) DEFAULT NULL,
`KeywordAlias` varchar(255) DEFAULT NULL,
`AccountTypeId` int(11) DEFAULT NULL,
`TribuneName` varchar(255) DEFAULT NULL,
`Division` varchar(255) DEFAULT NULL,
`ConferenceId` int(11) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`AddressLine2` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`State` varchar(255) DEFAULT NULL,
`Zip` varchar(255) DEFAULT NULL,
`Zip4` int(11) DEFAULT NULL,
`Country` varchar(255) DEFAULT NULL,
`Website` varchar(255) DEFAULT NULL,
`VideoLink` varchar(255) DEFAULT NULL,
`SiteProvider` varchar(255) DEFAULT NULL,
`VideoProvider` varchar(255) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`TimeZone` varchar(255) DEFAULT NULL,
`Colors` varchar(255) DEFAULT NULL,
`Logo` varchar(255) DEFAULT NULL,
`LogoImage` varchar(255) DEFAULT NULL,
`MonthlyFee` varchar(255) DEFAULT NULL,
`AnnualFee` varchar(255) DEFAULT NULL,
`RadioStation` varchar(255) DEFAULT NULL,
`TVStation` varchar(255) DEFAULT NULL,
`TicketOfficeLink` varchar(255) DEFAULT NULL,
`TicketOfficePhone` varchar(255) DEFAULT NULL,
`DST` varchar(255) DEFAULT NULL,
`Mascot` varchar(255) DEFAULT NULL,
`NCES_PSS_Id` varchar(255) DEFAULT NULL,
`County` varchar(255) DEFAULT NULL,
`CountyFipsId` varchar(255) DEFAULT NULL,
`Phone` varchar(255) DEFAULT NULL,
`PublicSchool` varchar(255) DEFAULT NULL,
`LocaleCode` varchar(255) DEFAULT NULL,
`NumberOfStudents` varchar(255) DEFAULT NULL,
`Enrollment_9to12` varchar(255) DEFAULT NULL,
`MaleStudents` varchar(255) DEFAULT NULL,
`FemaleStudents` varchar(255) DEFAULT NULL,
`Coed` varchar(255) DEFAULT NULL,
`LowestGradeLevel` varchar(255) DEFAULT NULL,
`HighestGradeLevel` varchar(255) DEFAULT NULL,
`Title1` varchar(255) DEFAULT NULL,
`SchoolType` varchar(255) DEFAULT NULL,
`SEC_Comb` varchar(255) DEFAULT NULL,
`Affiliation` varchar(255) DEFAULT NULL,
`SearchSortorder` varchar(255) DEFAULT NULL,
`CreationDate` varchar(255) DEFAULT NULL,
`CreatedBy` varchar(255) DEFAULT NULL,
`LastModifiedDate` varchar(255) DEFAULT NULL,
`LastModifiedBy` varchar(255) DEFAULT NULL,
`CompName` varchar(255) DEFAULT NULL,
`Show` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`boarding_school` tinyint(1) DEFAULT NULL,
`city_to_zip_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `AccountId` (`AccountId`),
KEY `NameIndexAccounts` (`Name`),
KEY `index_accounts_city_state` (`City`,`State`),
KEY `index_accounts_city_to_zip_id` (`city_to_zip_id`),
KEY `index_accounts_on_accounttypeid_and_name` (`AccountTypeId`,`Name`),
KEY `index_accounts_on_AccountTypeId` (`AccountTypeId`)
) ENGINE=InnoDB AUTO_INCREMENT=41567 DEFAULT CHARSET=utf8
accounts remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
Table: accounts
Create Table: CREATE TABLE `accounts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`AccountId` varchar(255) DEFAULT NULL,
`AccountIdTemp` varchar(255) DEFAULT NULL,
`HighSchoolTempId` varchar(255) DEFAULT NULL,
`IsActive` tinyint(1) DEFAULT NULL,
`IsVisible` tinyint(1) DEFAULT NULL,
`IsPaidAd` varchar(255) DEFAULT NULL,
`boolean` varchar(255) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`CommonName` varchar(255) DEFAULT NULL,
`ShortName` varchar(255) DEFAULT NULL,
`AbbreviatedName` varchar(255) DEFAULT NULL,
`OtherName` varchar(255) DEFAULT NULL,
`NickName` varchar(255) DEFAULT NULL,
`FemaleNickName` varchar(255) DEFAULT NULL,
`KeywordAlias` varchar(255) DEFAULT NULL,
`AccountTypeId` int(11) DEFAULT NULL,
`TribuneName` varchar(255) DEFAULT NULL,
`Division` varchar(255) DEFAULT NULL,
`ConferenceId` int(11) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`AddressLine2` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`State` varchar(255) DEFAULT NULL,
`Zip` varchar(255) DEFAULT NULL,
`Zip4` int(11) DEFAULT NULL,
`Country` varchar(255) DEFAULT NULL,
`Website` varchar(255) DEFAULT NULL,
`VideoLink` varchar(255) DEFAULT NULL,
`SiteProvider` varchar(255) DEFAULT NULL,
`VideoProvider` varchar(255) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`TimeZone` varchar(255) DEFAULT NULL,
`Colors` varchar(255) DEFAULT NULL,
`Logo` varchar(255) DEFAULT NULL,
`LogoImage` varchar(255) DEFAULT NULL,
`MonthlyFee` varchar(255) DEFAULT NULL,
`AnnualFee` varchar(255) DEFAULT NULL,
`RadioStation` varchar(255) DEFAULT NULL,
`TVStation` varchar(255) DEFAULT NULL,
`TicketOfficeLink` varchar(255) DEFAULT NULL,
`TicketOfficePhone` varchar(255) DEFAULT NULL,
`DST` varchar(255) DEFAULT NULL,
`Mascot` varchar(255) DEFAULT NULL,
`NCES_PSS_Id` varchar(255) DEFAULT NULL,
`County` varchar(255) DEFAULT NULL,
`CountyFipsId` varchar(255) DEFAULT NULL,
`Phone` varchar(255) DEFAULT NULL,
`PublicSchool` varchar(255) DEFAULT NULL,
`LocaleCode` varchar(255) DEFAULT NULL,
`NumberOfStudents` varchar(255) DEFAULT NULL,
`Enrollment_9to12` varchar(255) DEFAULT NULL,
`MaleStudents` varchar(255) DEFAULT NULL,
`FemaleStudents` varchar(255) DEFAULT NULL,
`Coed` varchar(255) DEFAULT NULL,
`LowestGradeLevel` varchar(255) DEFAULT NULL,
`HighestGradeLevel` varchar(255) DEFAULT NULL,
`Title1` varchar(255) DEFAULT NULL,
`SchoolType` varchar(255) DEFAULT NULL,
`SEC_Comb` varchar(255) DEFAULT NULL,
`Affiliation` varchar(255) DEFAULT NULL,
`SearchSortorder` varchar(255) DEFAULT NULL,
`CreationDate` varchar(255) DEFAULT NULL,
`CreatedBy` varchar(255) DEFAULT NULL,
`LastModifiedDate` varchar(255) DEFAULT NULL,
`LastModifiedBy` varchar(255) DEFAULT NULL,
`CompName` varchar(255) DEFAULT NULL,
`Show` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`boarding_school` tinyint(1) DEFAULT NULL,
`city_to_zip_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `AccountId` (`AccountId`),
KEY `NameIndexAccounts` (`Name`),
KEY `index_accounts_city_state` (`City`,`State`),
KEY `index_accounts_city_to_zip_id` (`city_to_zip_id`),
KEY `index_accounts_on_AccountTypeId` (`AccountTypeId`)
) ENGINE=InnoDB AUTO_INCREMENT=41569 DEFAULT CHARSET=utf8
explain local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: accounts
type: ref
possible_keys: PRIMARY,index_accounts_on_accounttypeid_and_name,index_accounts_on_AccountTypeId
key: index_accounts_on_AccountTypeId
key_len: 5
ref: const
rows: 2044
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: teams
type: ref
possible_keys: PRIMARY,index_teams_on_account_id,index_teams_on_sport_type_id
key: index_teams_on_account_id
key_len: 5
ref: mysql_development.accounts.id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: team_histories
type: ref
possible_keys: PRIMARY,team_history_team_id_index,team_history_academic_year
key: team_history_team_id_index
key_len: 5
ref: mysql_development.teams.id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: player_to_team_histories
type: ref
possible_keys: index_player_to_team_histories_on_player_id,index_player_to_team_histories_on_team_history_id,index_pth_valid_team_history
key: index_player_to_team_histories_on_team_history_id
key_len: 5
ref: mysql_development.team_histories.id
rows: 7
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: players
type: eq_ref
possible_keys: PRIMARY,index_players_on_hometown_state
key: PRIMARY
key_len: 4
ref: mysql_development.player_to_team_histories.player_id
rows: 1
Extra: Using where
5 rows in set (0.01 sec)
explain remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: accounts
type: ref
possible_keys: PRIMARY,index_accounts_on_AccountTypeId
key: index_accounts_on_AccountTypeId
key_len: 5
ref: const
rows: 3430
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: teams
type: ref
possible_keys: PRIMARY,index_teams_on_account_id,index_teams_on_sport_type_id
key: index_teams_on_account_id
key_len: 5
ref: lateral_sports_production.accounts.id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: team_histories
type: ref
possible_keys: PRIMARY,team_history_team_id_index,team_history_academic_year
key: team_history_team_id_index
key_len: 5
ref: lateral_sports_production.teams.id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: player_to_team_histories
type: ref
possible_keys: index_player_to_team_histories_on_player_id,index_player_to_team_histories_on_team_history_id
key: index_player_to_team_histories_on_team_history_id
key_len: 5
ref: lateral_sports_production.team_histories.id
rows: 6
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: players
type: eq_ref
possible_keys: PRIMARY,index_players_on_hometown_state
key: PRIMARY
key_len: 4
ref: lateral_sports_production.player_to_team_histories.player_id
rows: 1
Extra: Using where
5 rows in set (0.00 sec)
player_to_team_histories remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
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,
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`)
) ENGINE=InnoDB AUTO_INCREMENT=737082 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
player_to_team_history local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
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
players remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
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=556661 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
profile local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000098 |
| checking permissions | 0.000004 |
| checking permissions | 0.000001 |
| checking permissions | 0.000002 |
| checking permissions | 0.000001 |
| checking permissions | 0.000004 |
| Opening tables | 0.000030 |
| System lock | 0.000010 |
| init | 0.000063 |
| optimizing | 0.000022 |
| statistics | 0.049947 |
| preparing | 0.000043 |
| executing | 0.000032 |
| Sending data | 0.536235 |
| end | 0.000015 |
| removing tmp table | 0.000006 |
| end | 0.000003 |
| query end | 0.000004 |
| closing tables | 0.000013 |
| freeing items | 0.000018 |
| logging slow query | 0.000001 |
| cleaning up | 0.000004 |
+----------------------+----------+
profile remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000042 |
| checking query cache for query | 0.000206 |
| Opening tables | 0.000584 |
| System lock | 0.000017 |
| Table lock | 0.000015 |
| init | 0.000127 |
| optimizing | 0.000040 |
| statistics | 0.009145 |
| preparing | 0.000070 |
| executing | 0.000051 |
| Sending data | 2.591842 |
| end | 0.000026 |
| removing tmp table | 0.000016 |
| end | 0.000015 |
| query end | 0.000012 |
| freeing items | 0.000102 |
| logging slow query | 0.000011 |
| cleaning up | 0.000011 |
+--------------------------------+----------+
query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
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'))
record counts local
1 2 3 4 5
player_to_team_histories: 541,846
players: 474,353
team_histories: 60,508
teams: 48,325
accounts: 34,921
record counts remote
1 2 3 4 5
player_to_team_histories: 678,981
players: 551,170
team_histories: 63,669
teams: 51,448
accounts: 34,923
team_histories local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
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
team_histories remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
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_child_conference_id` (`child_conference_id`)
) ENGINE=MyISAM AUTO_INCREMENT=63913 DEFAULT CHARSET=latin1
teams local
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
Table: teams
Create Table: CREATE TABLE `teams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`IsActive` tinyint(1) DEFAULT NULL,
`IsVisible` tinyint(1) DEFAULT NULL,
`AccountId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`AccountIdTemp` int(11) DEFAULT NULL,
`Name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`sport_type_id` int(11) DEFAULT NULL,
`ConferenceId` int(11) DEFAULT NULL,
`Division` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`NationalRanking` int(11) 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,
`LeagueID` int(11) DEFAULT NULL,
`child_conference_id` int(11) DEFAULT NULL,
`Show` tinyint(1) DEFAULT NULL,
`NextLevel` int(11) DEFAULT NULL,
`HomeVenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_SportID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_SportName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_LeagueID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_LeagueName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_TeamID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`LoadID` int(11) DEFAULT NULL,
`RosterLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`ScheduleLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`account_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `AccountId` (`AccountId`),
KEY `index_teams_on_account_id` (`account_id`),
KEY `index_teams_on_sport_type_id` (`sport_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=79496 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
teams remote
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
Table: teams
Create Table: CREATE TABLE `teams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`IsActive` tinyint(1) DEFAULT NULL,
`IsVisible` tinyint(1) DEFAULT NULL,
`AccountId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`AccountIdTemp` int(11) DEFAULT NULL,
`Name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`sport_type_id` int(11) DEFAULT NULL,
`ConferenceId` int(11) DEFAULT NULL,
`Division` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`NationalRanking` int(11) 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,
`LeagueID` int(11) DEFAULT NULL,
`child_conference_id` int(11) DEFAULT NULL,
`Show` tinyint(1) DEFAULT NULL,
`NextLevel` int(11) DEFAULT NULL,
`HomeVenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_SportID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_SportName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_LeagueID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_LeagueName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SD_TeamID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`LoadID` int(11) DEFAULT NULL,
`RosterLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`ScheduleLink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`account_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `AccountId` (`AccountId`),
KEY `index_teams_on_account_id` (`account_id`),
KEY `index_teams_on_sport_type_id` (`sport_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=82619 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.