|
|
Posted by ojorus on 08/04/06 13:09
"Michael Boutros" <michael.boutros@gmail.com> skrev i melding
news:1154695402.357089.100150@i42g2000cwa.googlegroups.com...
>
> ojorus wrote:
>> 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)
>>
>> 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
>
> If I understand you correctly, you can use:
>
> SELECT score FROM table ORDER BY score DESC LIMIT 1
>
> I don't understand why you are using Time>CURDATE(), because it isn't
> needed in this situation.
>
> Thanks,
> Michael Boutros
>
Hi! Thanks for your replay.
The Score-table contain scores for many days. Since I want to get the
player's place for TODAY, I'll have to use Time>CURDATE(). (Which means
Time>'2006-08-04 00:00:00').
The query you propose will by the way only return the best score ever, which
I'm not interessted in. It is the player's PLACE for today I want, based on
his score.
Ole Johan
Navigation:
[Reply to this message]
|