mysql help [Archive] - Front Office Football Central

View Full Version : mysql help

akickku

02-19-2005, 10:06 PM

I'm running into some speed issues with mysql. I have 600000+ matches in my database currently. If I want to select a team's matches for one season, it takes around 6-8 seconds. I'm about ready to bite the bullet and seperate the database into smaller ones. Has anyone had any experience with this and/or recommendations on how to speed this up. It will take days to build my stats with this kind of speed :-/ I've tried: myisamchk --analyze which didn't really help as far as I can tell.

It took around a minute to build the stats for my club, considering the USA has around 5500 users :-/

Rockin the Suburbs (71753) akickku (545859) in Kansas
Overall Club Records
Club's highest rating: 182.0
Club's average rating: 115.79412
Opp.'s average rating: 100.77941
Average goals scored: 3.4411764
Average goals against: 2.0588236
Season 20 - V.197 (8570)
Club's highest rating: 92.0
Club's average rating: 80.7
Opp.'s average rating: 88.5
Average goals scored: 2.4
Average goals against: 2.9
Season 21 - VI.523 (27859)
Club's highest rating: 108.0
Club's average rating: 95.5
Opp.'s average rating: 52.785713
Average goals scored: 5.928571
Average goals against: 0.21428572
Season 22 - V.172 (8545)
Club's highest rating: 129.0
Club's average rating: 105.35714
Opp.'s average rating: 101.5
Average goals scored: 3.0714285
Average goals against: 2.9285715
Season 23 - V.172 (8545)
Club's highest rating: 149.0
Club's average rating: 135.28572
Opp.'s average rating: 124.07143
Average goals scored: 3.4285715
Average goals against: 2.9285715
Season 24 - V.172 (8545)
Club's highest rating: 165.0
Club's average rating: 142.85715
Opp.'s average rating: 127.5
Average goals scored: 2.2857144
Average goals against: 1.7142857
Season 25 - V.172 (8545)
Club's highest rating: 182.0
Club's average rating: 180.5
Opp.'s average rating: 143.0
Average goals scored: 2.0
Average goals against: 1.0

emmpee

02-20-2005, 03:16 AM

I know some SQL, but I'm a novice when it comes to performance. However, I've picked up a bit from overhearing colleagues at work.

My question/advice is, are you using indexes? If not, you probably should; if so, examine what you are using.

Sorry I can't be more helpful (I'm mostly a UI developer :D )

saintjo

02-20-2005, 07:57 AM

indexes make a big difference. also how your statement of formed makes a big difference. how and where you join, sub selects, etc. if you want to post the statement, we can give some more pointers.

akickku

02-20-2005, 06:18 PM

indexes where my problem. I was doing the following query:

select * from match_data where season = var1 and (home_team_id = var2 or away_team_id = var2) and (home_team_name = var3 or away_team_name = var3);

My only index was matchid's which was completely worthless for this query. I've got an index for season now which is making stuff go 4 times as fast, but that query is still taking a little over a second. Any more recommendations would be greatly appreciated.

Coffee Warlord

02-20-2005, 06:36 PM

Bigger it gets, slower it's gonna get. Fact of life. (And what doomed CTBB into requiring a total redesign, as it so happens.)

Axe the SELECT * and only select what you have to (ie what you're using on that page, save the select all for a detail link) when you're bringing up a big list.

Could also split the matches into seperate tables. New season, new table. Very simple to query off of.

saintjo

02-20-2005, 07:07 PM

you can index home and away id's as well. basically index any thing you would search on heavily.

dont split the tables by season. if you really need the perfomance boost, you can, but it would be poor db design.

also, i dont know if it will speed it up, but i personally wouldnt written the select statement like that. its not wrong, probably just personal preference. dont use the *, get the specific fields, lets just assume you need match_id

SELECT match_data.match_id
FROM match_data
WHERE
season = var1 and
(home_team_id in (var2,var3) or
away_team_id in (var2,var3));

also you dont mention it, but are you running this as a statement from within a program or compiling it as a stored proc? compiling it as a stored proc will let the db make some optimizations and save some front end set up time every time its run.

and most of all, never name a variable var[number]. it doesnt slow it down, but it looks bad :)

akickku

02-21-2005, 11:35 AM

and most of all, never name a variable var[number]. it doesnt slow it down, but it looks bad :)

That what this thing looks like in java, I'd thought I'd try and make it a bit more human readable because this peice of junk is not

String query = "select series_id, series_name, home_team_id, home_hatstat_rating, away_hatstat_rating, home_team_goals, away_team_goals from match_data where season = " + season + " and (home_team_id = " + team_id + " or away_team_id = " + team_id + ") and (home_team_name = \"" + team_name + "\" or away_team_name = \"" + team_name + "\");";

saintjo

02-21-2005, 12:18 PM

actually it is more readable to me because i can see you are running it as straight sql instead of a stored proc :)

try it as a stored proc, i bet it knocks off a slight amount of time.

akickku

02-21-2005, 02:03 PM

What is a stored proc? Everything I know about mysql is what I've managed to teach myself. Here's what a part of what I'll eventually be doing for team's in the US.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<style type="text/css">
BODY {COLOR: #000000; BACKGROUND-COLOR: #ffffff}
BODY {FONT-FAMILY: sans-serif}
TD {FONT-FAMILY: sans-serif}
TH {FONT-FAMILY: sans-serif}
H1 {FONT-FAMILY: sans-serif}
H2 {FONT-FAMILY: sans-serif}
PRE {MARGIN: 0px; FONT-FAMILY: monospace}
A:link {COLOR: #000099; BACKGROUND-COLOR: #ffffff; TEXT-DECORATION: none}
A:hover {TEXT-DECORATION: underline}
TABLE {BORDER-COLLAPSE: collapse}
.center {TEXT-ALIGN: center}
.center TABLE {MARGIN-LEFT: auto; MARGIN-RIGHT: auto; TEXT-ALIGN: left}
.center TH {TEXT-ALIGN: center! important}
TD {BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; FONT-SIZE: 75%; VERTICAL-ALIGN: baseline; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid}
TH {BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; FONT-SIZE: 75%; VERTICAL-ALIGN: baseline; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid}
H1 {FONT-SIZE: 150%}
H2 {FONT-SIZE: 125%}
.p {TEXT-ALIGN: left}
.e {FONT-WEIGHT: bold; COLOR: #000000; BACKGROUND-COLOR: #ccccff}
.h {FONT-WEIGHT: bold; COLOR: #000000; BACKGROUND-COLOR: #9999cc}
.v {COLOR: #000000; BACKGROUND-COLOR: #cccccc}
I {COLOR: #666666; BACKGROUND-COLOR: #cccccc}
IMG {BORDER-RIGHT: 0px; BORDER-TOP: 0px; FLOAT: right; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px}
HR {BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; WIDTH: 600px; COLOR: #000000; BORDER-BOTTOM: 0px; HEIGHT: 1px; BACKGROUND-COLOR: #cccccc}
</style>

<head>
<title></title>
</head>
<body>Rockin the Suburbs (71753) owned by akicku (545859) in Kansas 
<table border="1" cellpadding="3" cellspacing="0" width="95%">
<tbody>

<tr bordercolor="#000000" bgcolor="#9999cc">

<td> Season</td>
<td> Series</td>
<td> Position</td>
<td> Goals For</td>
<td> Goals Against</td>

<td> Avg. Goals For</td>
<td> Avg. Goals Against</td>
<td> Wins</td>
<td> Losses</td>
<td> Ties</td>

<td> Highest Rating</td>
<td> Avg. Rating</td>
<td> Opp. Avg. Rating</td>
</tr>

<tr bordercolor="#000000" bgcolor="#ccccff">
<td> 20</td>

<td> V.197</td>
<td> 5th</td>
<td> 24</td>
<td> 29</td>
<td> 2.4</td>

<td> 2.9</td>
<td> 4</td>
<td> 5</td>
<td> 1</td>
<td> 92.0</td>

<td> 80.7</td>
<td> 88.5</td>
</tr>
<tr bordercolor="#000000" bgcolor="#cccccc">
<td> 21</td>
<td> VI.523</td>

<td> 1st</td>
<td> 83</td>
<td> 3</td>
<td> 5.928571</td>
<td> 0.21428572</td>

<td> 14</td>
<td> 0</td>
<td> 0</td>
<td> 108.0</td>
<td> 95.5</td>

<td> 52.79</td>
</tr>
<tr bordercolor="#000000" bgcolor="#ccccff">
<td> 22</td>
<td> V.172</td>
<td> 6th</td>

<td> 43</td>
<td> 41</td>
<td> 3.0714285</td>
<td> 2.9285715</td>
<td> 6</td>

<td> 8</td>
<td> 0</td>
<td> 129.0</td>
<td> 105.38</td>
<td> 101.5</td>

</tr>
<tr bordercolor="#000000" bgcolor="#cccccc">
<td> 23</td>
<td> V.172</td>
<td> 5th</td>
<td> 48</td>

<td> 41</td>
<td> 3.4285715</td>
<td> 2.9285715</td>
<td> 6</td>
<td> 6</td>

<td> 2</td>
<td> 149.0</td>
<td> 135.23</td>
<td> 124.07</td>
</tr>

<tr bordercolor="#000000" bgcolor="#ccccff">
<td> 24</td>
<td> V.172</td>
<td> 4th</td>
<td> 32</td>

<td> 24</td>
<td> 2.2857144</td>
<td> 1.7142857</td>
<td> 8</td>
<td> 4</td>

<td> 2</td>
<td> 165.0</td>
<td> 142.86</td>
<td> 127.5</td>
</tr>

<tr bordercolor="#000000" bgcolor="#cccccc">
<td> 25</td>
<td> V.172</td>
<td> 3rd</td>
<td> 6</td>

<td> 2</td>
<td> 2.0</td>
<td> 1.0</td>
<td> 2</td>
<td> 1</td>

<td> 0</td>
<td> 182.0</td>
<td> 176.0</td>
<td> 145.33</td>
</tr>

<tr bordercolor="#000000" bgcolor="#9999cc">
<td> Totals</td>
<td> -</td>
<td> -</td>
<td> 240</td>

<td> 142</td>
<td> 3.4411764</td>
<td> 2.0588236</td>
<td> 39 (57.35%)</td>
<td> 24 (35.29%)</td>

<td> 5 (7.35%)</td>
<td> 182.0</td>
<td> 115.79</td>
<td> 100.78</td>
</tr>

</tbody>
</table>

</body>
</html>

saintjo

02-21-2005, 03:21 PM

a stored proc is your sql statement but made into a function in the database. just like you can make functions in java, you can make them in mysql. the db compiles the statement and makes optimizations and checks once instead of everytime you run it.

akickku

02-21-2005, 04:10 PM

Cool, I'll google research how to do that. Thanks for the pointers.

Mr. Wednesday

02-21-2005, 11:54 PM

The official documentation:
http://dev.mysql.com/doc/mysql/en/stored-procedures.html