在MySQL中选择多行的前十个条目

Question

I've got a table which manages user scores, e.g.:

    id   scoreA   scoreB    ...   scoreX
------  -------  -------    ...  -------
     1      ...      ...    ...      ...
     2      ...      ...    ...      ...

Now i wanted to create a scoreboard which can be sorted by each of the scores (only descending).

However, I can't just query the entries and send them to the client (which renders them with Javascript) as the table contains thousands of entries and sending all of those entries to the client would create unreasonable traffic.

I came to the conclusion that all non-relevant entries (entries which may not show up in the scoreboard as the score is too low) should be discarded on the server-side with the following rule of thumb:

If any of the scores is within the top ten for this specific score keep the entry.
If none of the scores is within the top ten for this specific score discard it.

Now I ran into the question if this can be done efficiently with (My)SQL or if this processing should take place in the php-code querying the database to keep the whole thing performant.

Any help is greatly appreciated!

Answer 1

Go with rows, not columns, for storing scores. Have composite index on userid,score. A datetime column could also be useful. Consider not having the top 10 snapshot table anyway, just the lookup that you suggest. So an order by score desc and Limit 10 in query.

Not that the below reference is the authority on Covering Indexes, but to throw the term out there for your investigation. Good luck.