[FIXED] StatsLab Team Warroom Slow

[ruffyen]

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.

[anthonywvalentine]

Originally Posted by ruffyen

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):
monitoring - How can I view live MySQL queries? - Stack Overflow -- 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.

For someone who this is complete gibberish too... What exactly do I need to edit to help speed up the Team Warroom?

[ruffyen]

@anthonywvalentine

So rename the attached file from .txt to .sql

Upload it into your SQL files directory on the server (same place all the other SQL files go)

and when you go to run the sql files to update stats lab it should run this one last due to the name of the file.