OOTP Developments Forums - View Single Post

ruffyen · 10-29-2016, 05:21 PM

So I have been fighting team warroom slowness. It was taking upwards of 3 mins just to load the players eligible for the draft. I did some performance debug sessions and the following two query seemed to drastically help

create index idx_player_id on players_fielding(player_id);

create index idx_player_id on players_batting(player_id);
create index idx_player_id on amateur_draft(player_id);
create index idx_player_id on players_pitching(player_id);
create index idx_player_id on ootp_sql_draft_list(player_id);
create index idx_player_id on players(player_id);

The query went from taking over 2 mins to run to running in around 1 sec for a league with around 13 years of play data.

I love StatsLab and I hope that this little diddy will help anyone who is starting to see things take longer and longer.

If you are curious of the steps taken I used the following resource(s):
http://stackoverflow.com/a/7470567 -- This was for finding the query
https://www.sitepoint.com/using-expl...mysql-queries/ -- This was for the query analysis

Edit: After the first IDX that I created i started digging in deeper after we switched to showing potentials / ratings / stats in statslab draft and found additional tables to index on player_id. The queries are now sub second...0.04 secs for 500 rows.

10-29-2016, 05:21 PM	#1
ruffyen Minors (Single A) Join Date: Jan 2016 Posts: 61	[FIXED] StatsLab Team Warroom Slow So I have been fighting team warroom slowness. It was taking upwards of 3 mins just to load the players eligible for the draft. I did some performance debug sessions and the following two query seemed to drastically help create index idx_player_id on players_fielding(player_id); create index idx_player_id on players_batting(player_id); create index idx_player_id on amateur_draft(player_id); create index idx_player_id on players_pitching(player_id); create index idx_player_id on ootp_sql_draft_list(player_id); create index idx_player_id on players(player_id); The query went from taking over 2 mins to run to running in around 1 sec for a league with around 13 years of play data. I love StatsLab and I hope that this little diddy will help anyone who is starting to see things take longer and longer. If you are curious of the steps taken I used the following resource(s): http://stackoverflow.com/a/7470567 -- This was for finding the query https://www.sitepoint.com/using-expl...mysql-queries/ -- This was for the query analysis Edit: After the first IDX that I created i started digging in deeper after we switched to showing potentials / ratings / stats in statslab draft and found additional tables to index on player_id. The queries are now sub second...0.04 secs for 500 rows. Last edited by ruffyen; 11-01-2016 at 07:16 AM.