"Missing" indexes in MySQL

[BMW]

I have "Missing" with quotes, because there's always the tradeoff between storage space/performance.

There were a number of tables that are missing PRIMARY and/or indexes. Running these against your database should fix them up.

I haven't used these for an extended period of time, but I believe the worst error you would be likely to run into is that it cannot apply the index if there is a repeated key (i.e. In human_manager_history, there is more than one record for the same Manager, Managing the same Team, in the same Year. Yes, the way OOTP works, this should not happen, but a database cares not about OOTP and how managers work, it's just looking at a bunch of numbers.)

I didn't use a PRIMARY on the players_at_bat_batting_stats table, because even it doesn't happen often, a batter could face a pitcher twice within the same inning.

	ALTER TABLE `human_manager_history` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `human_manager_history_batting_stats` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `human_manager_history_fielding_stats_stats` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `human_manager_history_financials` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `human_manager_history_pitching_stats` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `human_manager_history_record` ADD PRIMARY KEY ( `human_manager_id` , `team_id` , `year` ) ;
	ALTER TABLE `league_events` ADD PRIMARY KEY ( `league_id` , `start_date` , `type` ) ;
	ALTER TABLE `league_history` ADD PRIMARY KEY ( `league_id` , `sub_league_id` , `year` ) ;
	ALTER TABLE `league_history_all_star` ADD PRIMARY KEY ( `league_id` , `sub_league_id` , `year` , `all_star_pos` ) ;
	ALTER TABLE `league_history_batting_stats` ADD PRIMARY KEY ( `year` , `league_id` ) ;
	ALTER TABLE `league_history_fielding_stats` ADD PRIMARY KEY ( `year` , `league_id` , `sub_league_id` ) ;
	ALTER TABLE `league_history_pitching_stats` ADD PRIMARY KEY ( `year` , `league_id` ) ;
	ALTER TABLE `league_playoffs` ADD PRIMARY KEY ( `league_id` ) ;
	ALTER TABLE `league_playoff_fixtures` ADD PRIMARY KEY ( `league_id` , `team_id0` , `team_id1` ) ;
	ALTER TABLE `players_at_bat_batting_stats` ADD INDEX ( `player_id` ) ;
	ALTER TABLE `players_at_bat_batting_stats` ADD INDEX ( `game_id` ) ;
	ALTER TABLE `players_at_bat_batting_stats` ADD INDEX ( `opponent_player_id` ) ;
	ALTER TABLE `players_at_bat_batting_stats` ADD INDEX ( `team_id` ) ;
	ALTER TABLE `players_awards` ADD PRIMARY KEY ( `player_id` , `award_id` , `year` , `day` , `month` ) ;
	ALTER TABLE `players_career_batting_stats` ADD PRIMARY KEY ( `player_id` , `year` , `team_id` , `split_id` ) ;
	ALTER TABLE `players_career_fielding_stats` ADD PRIMARY KEY ( `player_id` , `year` , `team_id` , `split_id` , `position` ) ;
	ALTER TABLE `players_career_pitching_stats` ADD PRIMARY KEY ( `player_id` , `year` , `team_id` , `split_id` ) ;
	ALTER TABLE `players_game_batting` ADD PRIMARY KEY ( `player_id` , `year` , `team_id` , `game_id` ) ;
	ALTER TABLE `players_game_pitching_stats` ADD PRIMARY KEY ( `player_id` , `year` , `team_id` , `game_id` ) ;
	ALTER TABLE `players_individual_batting_stats` ADD PRIMARY KEY ( `player_id` , `opponent_id` ) ;
	ALTER TABLE `players_league_leader` ADD PRIMARY KEY ( `player_id` , `sub_league_id` , `year` , `category` ) ;
	ALTER TABLE `players_streak` ADD PRIMARY KEY ( `player_id` , `league_id` , `streak_id` , `started` ) ;

[Comedian2004]

Some of the tables are not really tables, but are in a sort of way. What I mean is, they are treated as tables, but there is no look up file.

Good example is the pitchers 'pitches', there is no dump that contains the actual pitchers, 'fastball, slider, etc.', but it is treated like a table.

Is this what you mean?

[BMW]

Originally Posted by Comedian2004

Some of the tables are not really tables, but are in a sort of way. What I mean is, they are treated as tables, but there is no look up file.

Good example is the pitchers 'pitches', there is no dump that contains the actual pitchers, 'fastball, slider, etc.', but it is treated like a table.

Is this what you mean?

I know what you're saying... There are some fields that are enumerations (i.e. they hold a value that corresponds to items on a list, but those items are not stored in a table), but I'm not certain what it has to do with indexing tables.

For the queries above, all that is required is that you take a full MySQL file dump and run the files against your MySQL database. From that point on, once they become a physical table in a MySQL database, I am considering that a table.

The code just creates some indexes on those tables, which will enable them to run a little faster when you are doing JOINs or other routine things.