|
|
Posted by Rik on 08/04/06 13:43
ojorus wrote:
> "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.
Is it just 1 decimal? In that case I'd suggest just multiplying it with 10
and store it like an integer.
The same goes for Time, if it's OK to have just a date, just store it like
an integer with date in YYYYMMDD format.
> 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.
It surely will, with a simple cronjob, but if you want to get info from past
days (which you want to save I presume, as you're keeping them in the
database), you'll have the same problem, only less often.
> Thanks for the mysql-group-info.
NP
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|