CODEWITHSUNDEEP
phpmysqldatabase
Roman
Roman

Reputation: 2079

effectively sort big mysql table

Say, I have a table with 1-2 millions of records. I have API to sort result array and retrieve first 25 rows. Basically, it´s a scoreboard table, so there may be many requests at a time. My question is what should I do to optimize client-server data flow? For example, how to: 1. sort table itself, not result array, to just retrieve top 25 results? 2. speed up requests? My sample code is below.

mysql_connect(HOST, MYSQL_USER, MYSQL_PASS);

mysql_select_db(DATABASE);
$sql = mysql_query("select pin,country,nickname,score from scoreboard order by score desc limit 0, 25");

 while($row = mysql_fetch_assoc($sql)) $output[] = $row;
 $resulting_array['players'] = $output;
 print(json_encode($resulting_array));
 mysql_close();

Upvotes: 0

Views: 193

Answers (1)

ajreal
ajreal

Reputation: 47321

  1. build an index on column score
  2. cache the result into a storage (like memcache, disk-cache, etc ...) for subsequent access
  3. write a hook to purge / expire cache when the score has been updated

Upvotes: 3

Related Questions