Mysql sorting by -

i'm working on how implement leaderboard. i'd able sort table several different filters(score,number of submissions, average). table might this.

+--------+-----------------------+------+-----+---------+-------+ | field  | type                  | null | key | default | | +--------+-----------------------+------+-----+---------+-------+ | userid | mediumint(8) unsigned | no   | pri | 0       |       | | score  | int                   | yes  | mul | null    |       | | numsub | int                   | yes  | mul | null    |       |  +--------+-----------------------+------+-----+---------+-------+ 

and sample set of data so:

+--------+----------+--------+ | userid | score    | numsub | +--------+----------+--------+ | 505610 | 1245     | 2      |     | 544222 | 1458     | 2      | | 547278 | 245      | 1      | | 659241 | 12487    | 8      | | 681087 | 5487     | 3      | +--------+----------+--------+ 

my queries coming php.

// top 100 scores $q = "select userid, score table order score desc limit 0, 100"; 

this return set of userid/score sorted highest score first have query sort numsub (number of submissions)

what sort table avg score being score/numsub; table large efficiency important me.

thanks in advance!

if efficiency important, add column avgscore , assign value of score/numsub. then, create index on column.

you can use insert/update trigger average calculation automatically when row added or modified.

once tables gets large, sort going take noticeable amount of time.