-
-
Save workmaster2n/4270389 to your computer and use it in GitHub Desktop.
This query runs 5x slower on my remote staging server than it does on my local dev machine
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| *************************** 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| *************************** 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| +----------------------+----------+ | |
| | 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 | | |
| +----------------------+----------+ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| +--------------------------------+----------+ | |
| | 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 | | |
| +--------------------------------+----------+ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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')) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| player_to_team_histories: 541,846 | |
| players: 474,353 | |
| team_histories: 60,508 | |
| teams: 48,325 | |
| accounts: 34,921 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| player_to_team_histories: 678,981 | |
| players: 551,170 | |
| team_histories: 63,669 | |
| teams: 51,448 | |
| accounts: 34,923 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment