|
|
Posted by Rik on 08/04/06 13:10
Michael Boutros wrote:
> 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.
That's not what the op wants.
To rephrase what I gather from his post:
He wants the placing of the score compared to other scores on that
particular day.
"SELECT COUNT(*) FROM table WHERE Score>=$score AND Time>CURDATE()"
Would be my choice, but seems to slow. Indexes are already created according
to the OP, so that's no way to go either. Maybe save only the day of the
score in the YYYYMMDD format, and use Time=CURDATE()+0.
About using COUNT(*):
"COUNT(*) is optimized to return very quickly if the SELECT retrieves from
one table, no other columns are retrieved, and there is no WHERE clause."
There is a WHERE clause here, hence the optimization is lost. So, maybe use
COUNT(score). I don't know wether this will be faster, but it's worth a
shot.
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|