0

I have got a simple game: players earn points, what I store in the POINTS table. I'm afraid the table will be too big, so I want to summarize the points of last month because optimalizing.

For example ID 42 player has got 120 records in the table between 01.08.2019 and 31.08.2019, with 560 points. I would like to delete the 120 rows, and insert only one, with 560 points. I am using PHP(5.6.40) and MySQL.

I have no idea how to solve it. Should I make an archive table, copy the records to it from original table, delete the copied records from original table, and insert back the summarized records only?

POINTS table

id : auto inc. int
timestamp : (unsigned) int
playerid : int
points : tinyint

DevinPHP
  • 37
  • 3
  • 2
    Premature optimization is the root of all evil. It's harder to maintain the system and debug the system. Optimize the parts that are slow when they are slow. :-) There are people with millions of rows and no problems (https://stackoverflow.com/a/1995078/3103271). Keep in mind, that an int is only some bytes. Even with 2^30 records it only needs some GB space. – Charlie Aug 31 '19 at 20:13

2 Answers2

2

Do not delete the data from your tables unless there is a very good reason to do so. You want to keep all the data and aggregate it in some other way. What if you make a mistake or a bug and you would like to recalculate the scores?

Generally the SQL aggregate function SUM(score) works really well with indexes and you might have thousands of rows and sum them up on the fly with no perceivable performance reduction.

If you expect a lot of rows in your table you could create an aggregate table and keep the summed up values in it. For example add another column to your players table called total_score and every new record you add for that player in points table will be added to the running total of total_score. You could handle this in your application layer or use DB triggers to update the totals automatically.

Dharman
  • 30,962
  • 25
  • 85
  • 135
2

You can create a separate table for one maintaining summary of points. Do not delete the master table as it will serve as a point of reference in future if something goes wrong.

Create new table with same structure 

CREATE table points_sum LIKE points

Then do one time cleaning to summarize points

INSERT INTO points_sum VALUES (
     SELECT id, timestamp, playerid, SUM(points) AS points
     FROM points
     GROUP BY playerid, FROM_UNIXTIME(timestamp, '%Y-%m')
)

Later all values you store should be summed up before inserting/updating into the table. If player information is not present for the current month, then INSERT else UPDATE. Insert is pretty simple. Here is how you would update it.

UPDATE points_sum
SET points = points + 50 
WHERE playerid = '123'
AND timestamp >= UNIX_TIMESTAMP('2018-08-01 00:00:00')
AND timestamp <= UNIX_TIMESTAMP('2018-08-31 23:59:59')
Samir Selia
  • 7,007
  • 2
  • 11
  • 30