mysql help

[akickku]

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]

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 )

[saintjo]

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]

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]

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]

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]

Originally Posted by saintjo

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]

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]

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.









Rockin the Suburbs (71753) owned by akicku (545859) in Kansas

Season	Series	Position	Goals For	Goals Against	Avg. Goals For	Avg. Goals Against	Wins	Losses	Ties	Highest Rating	Avg. Rating	Opp. Avg. Rating
20	V.197	5th	24	29	2.4	2.9	4	5	1	92.0	80.7	88.5
21	VI.523	1st	83	3	5.928571	0.21428572	14	0	0	108.0	95.5	52.79
22	V.172	6th	43	41	3.0714285	2.9285715	6	8	0	129.0	105.38	101.5
23	V.172	5th	48	41	3.4285715	2.9285715	6	6	2	149.0	135.23	124.07
24	V.172	4th	32	24	2.2857144	1.7142857	8	4	2	165.0	142.86	127.5
25	V.172	3rd	6	2	2.0	1.0	2	1	0	182.0	176.0	145.33
Totals	-	-	240	142	3.4411764	2.0588236	39 (57.35%)	24 (35.29%)	5 (7.35%)	182.0	115.79	100.78

[saintjo]

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]

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

[Mr. Wednesday]

The official documentation:
http://dev.mysql.com/doc/mysql/en/st...rocedures.html