469,909 Members | 1,571 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

performance questions

Hi!
My company make several flash-based games, and I use php to communicate
with
mysql to provide highscore-lists.

My problem is this:
When I save a player's score in the mysql-table, I want to find which
place
the player got with his score (today). To get this I have tried two
different solutions, which both works, but are very ineffective: (The
Time-field is a DateTime type, and I have Score and Time as Indexes.
The Score-field is a DECIMAL (20,2))

1) SELECT COUNT(*) FROM table WHERE Score>=$score AND Time>CURDATE().
- or -
2) SELECT Score FROM table WHERE Score>=$score AND Time>CURDATE().

.... $place = mysql_num_rows($result)

Both give the right result, but the problem is that when there are many

players playing at the same time, and the table consists of several
million
records, the query is just too heavy for the mysql-server, and it
breakes
down.

So my question is: Are there any better ways of getting a player's
place?

Ole Johan, Norway

Aug 4 '06 #1
1 1887
"ojorus" <oj****@hotmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Hi!
My company make several flash-based games, and I use php to communicate
with
mysql to provide highscore-lists.

My problem is this:
When I save a player's score in the mysql-table, I want to find which
place
the player got with his score (today). To get this I have tried two
different solutions, which both works, but are very ineffective: (The
Time-field is a DateTime type, and I have Score and Time as Indexes.
The Score-field is a DECIMAL (20,2))

1) SELECT COUNT(*) FROM table WHERE Score>=$score AND Time>CURDATE().
- or -
2) SELECT Score FROM table WHERE Score>=$score AND Time>CURDATE().

... $place = mysql_num_rows($result)

Both give the right result, but the problem is that when there are many

players playing at the same time, and the table consists of several
million
records, the query is just too heavy for the mysql-server, and it
breakes
down.

So my question is: Are there any better ways of getting a player's
place?

Ole Johan, Norway
Unfortunately, both those solutions must search the entire db looking for
'Score >= $score'... you probably need to divide your db into smaller tables
of score ranges so that the queries don't take as long:

table names:

Scores_0thru100
Scores_101thru200
....

well, you get the idea. Then just place some PHP logic to
INSERT/UPDATE/SELECT the $score into/from the proper table.

Norm
Aug 6 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

25 posts views Thread by Brian Patterson | last post: by
reply views Thread by Jack Coxen | last post: by
1 post views Thread by Tim Smith | last post: by
115 posts views Thread by Mark Shelor | last post: by
13 posts views Thread by bjarne | last post: by
2 posts views Thread by 1944USA | last post: by
11 posts views Thread by ZenRhapsody | last post: by
8 posts views Thread by Gary Wessle | last post: by
1 post views Thread by jehugaleahsa | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.