1

I am creating a high scores table stored on a database for my game, but I was wondering the best practices are for storing such tables. Should the table be resorted each time a new score is added or should the data being received by a query just be sorted?

It seems like it would be easier on the server to just sort when retrieving data instead of updating the table each time.

rasen58
  • 4,672
  • 8
  • 39
  • 74
  • data in a db can be stored "sorted" in the on-disk files, but such an optimization is only useful if your data is only RARELY changed/updated, because any such change would require re-writing the entire on-disk image with the new ordering. for a normal table, it'd be a hideously inefficient process. – Marc B Feb 05 '14 at 03:59
  • Use an index, this [question may help][1] [1]: http://stackoverflow.com/questions/13944946/how-do-i-get-mysql-to-use-an-index-for-view-query – Leo Feb 05 '14 at 04:04
  • @MarcB Adding a new score is inserting one row. That doesn't 'require re-writing the entire on-disk image with the new ordering'. It requires an index update. – user207421 Feb 05 '14 at 05:08
  • ejp: yes, but the question is about storing the data pre-sorted. if you've got 1,2,5,10 and insert 6, then the requirement wouldn't allow 1,2,5,10,6 to appear on disk - you'd have to a chunk of the table for 1,2,5,6,10 – Marc B Feb 05 '14 at 18:31

3 Answers3

2

Rows in a relational database such as MySQL, Oracle, PostgreSQL, etc. are not maintained in any order. In the theory of relational databases result sets are returned in no specified order unless the query contains an ORDER BY clause. Any ordering is (must be) applied each time the data is retrieved.

Implementations may, in some cases, store the data in some order, but they are not required to do so. In fact, if you run the exact same query twice on the same data there is no guarantee that the data will be returned in the same sequence.

In other words, you cannot impose a storage order on your data, you impose order only on result sets at the time the query is executed.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
1

I recommend sorting the data in your MySQL query. As you said it is easier to only sort when needed, not when every record is added.

Hurricane Development
  • 2,449
  • 1
  • 19
  • 40
1

Data in tables are unsorted. The actual physical order of rows in a relational table is undetermined. However, some databases will order rows on disks according to a clustered index.

If your tables contain a few thousand rows, two approaches are not much different about performance. However, if your tables are around more than 10,000 rows, you can use clustered index. ( for reference about clustered index, http://www.karafilis.net/sql-indexing-part2/).

joy
  • 130
  • 3