|
|
Posted by Rik on 08/04/06 13:13
Rik wrote:
> 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.
Adding:
Is score an integer? That's how the database will work best. If it isn't an
integer, consider making it one.
BTW: if this isn't a solution, try (alt.)comp.databases.mysql. Those guys
will know a lot more about mysql optimization.
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|