Home | Webstore
Latest News: OOTP Update 20.5.47 - OOTP 20 Available - FHM 5 Available

Collection Missions for Perfect Team Released!

  

Go Back   OOTP Developments Forums > Out of the Park Baseball 20 > OOTP Mods > OOTP Mods - Database Tools

OOTP Mods - Database Tools Do you need to take a dump? SQL gurus welcome

Reply
 
Thread Tools
Old 10-29-2016, 05:21 PM   #1
ruffyen
Minors (Rookie Ball)
 
Join Date: Jan 2016
Posts: 48
Thanks: 3
Thanked 15x in 8 posts
[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.
ruffyen is offline   Reply With Quote
Thank you for this post:
silverwolf (12-20-2016)
Old 02-14-2017, 09:34 PM   #2
anthonywvalentine
Major Leagues
 
anthonywvalentine's Avatar
 
Join Date: Jul 2010
Location: Connecticut
Posts: 380
Thanks: 90
Thanked 104x in 62 posts
Quote:
Originally Posted by ruffyen View Post
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?
anthonywvalentine is offline   Reply With Quote
Old 02-17-2017, 01:15 PM   #3
ruffyen
Minors (Rookie Ball)
 
Join Date: Jan 2016
Posts: 48
Thanks: 3
Thanked 15x in 8 posts
@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.
Attached Files
File Type: txt z_create_indexes.mysql.txt (232 Bytes, 104 views)
ruffyen is offline   Reply With Quote
2 thanks for this post:
anthonywvalentine (02-21-2017), sjgmoney (04-11-2017)
Reply

Bookmarks

Tags
index, indexes, performance, statslab, warroom

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 05:27 PM.

 

Major League Baseball trademarks and copyrights are used with permission of MLB Advanced Media, L.P. Minor League Baseball trademarks and copyrights are used with the permission of Minor League Baseball. All rights reserved.

The Major League Baseball Players Association (www.MLBPLAYERS.com ) is the collective bargaining representative for all professional baseball players of the thirty Major League Baseball teams and serves as the exclusive group licensing agent for commercial and licensing activities involving active Major League baseball players. On behalf of its members, it operates the Players Choice licensing program and the Players Choice Awards, which benefit the needy through the Major League Baseball Players Trust, a charitable foundation established and run entirely by Major League baseball players. Follow: @MLB_Players; @MLBPAClubhouse; @MLBPlayersTrust

Out of the Park Baseball is a registered trademark of Out of the Park Developments GmbH & Co. KG

Google Play is a trademark of Google Inc.

Apple, iPhone, iPod touch and iPad are trademarks of Apple Inc., registered in the U.S. and other countries.

COPYRIGHT © 2017 OUT OF THE PARK DEVELOPMENTS. ALL RIGHTS RESERVED.

 

Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Copyright © 2018 Out of the Park Developments