Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
This query runs 5x slower on my remote staging server than it does on my local dev machine
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
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
*************************** 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)
*************************** 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)
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
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
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
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
+----------------------+----------+
| 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 |
+----------------------+----------+
+--------------------------------+----------+
| 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 |
+--------------------------------+----------+
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'))
player_to_team_histories: 541,846
players: 474,353
team_histories: 60,508
teams: 48,325
accounts: 34,921
player_to_team_histories: 678,981
players: 551,170
team_histories: 63,669
teams: 51,448
accounts: 34,923
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
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
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)
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
Something went wrong with that request. Please try again.