|
|
Posted by ojorus on 08/04/06 13:22
"Rik" <luiheidsgoeroe@hotmail.com> skrev i melding
news:18e9$44d347b3$8259c69c$8328@news2.tudelft.nl...
> 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
>
>
No, unfortunately the score is defined as decimal (20,2). So that's probably
one of the slow-down factors.
One solution to improve performance could be to have a table just containing
today's games, which will be cleared every midnight. Then I can skip the
CURDATE-thing, and the search will probably be a lot faster.
Thanks for the mysql-group-info.
Ole Johan
Navigation:
[Reply to this message]
|