How to write efficient MySQL query to delete specific rows depending on constraints
I got a question regarding my MySQL-database and would like to get input on what would be most efficient.
My problem is as follows,
I'm developing premium functionality for my board game web site. One premium functionality would be that all the games a user has played would be stored "forever" (for the user to look up afterwards). For normal users games older than 18 months are deleted.
Now I need to figure out an effective way to delete the games (which is more than 18 months old) for normal non premium users and keep games for premium users.
Simplifying things I got two tables (in reality there's one more table which stores the game participants for each game):
id=INT play_date=DATETIME end_score=INT player_id_1=INT player_id_2=INT
id=INT premium=BOOLEAN (true=enabled, false=not enabled)
The user table contains 300.000+ rows while the Games table contain a few million rows. Each day approx 20.000 games are added to the Games table.
What would be the most efficient way to remove games older than 18 months from NON-premium users.
So far we've removed games older than 18 months for ALL users each Monday morning.
Now I need to take premium-value and game date into account.
A few solutions(?):
- JOIN'ing the tables, altough we're talking million of rows in the Games table, this would be a no-no?
- Get each game-entry older than 18 months, then get each users entry from player_id_1 & player_id_2 and if ANYONE of these are premium, let the game be, else delete it if it's older than 18 months. So for one week this could be 20k*7=140k worth of games.
- Above solution except I do it every hour. Then there's approx 1000 games I need to get and check.
- ?? add some kind of helper variable to the Games table? But what if a user stops using premium....
Any tips welcome...
Use an expire date and an index over that.
Allow NULL on that column.
Premium users will have NULL on their games.
Deleting games with expire_date < sysdate will use the index, it is, a INDEX RANGE SCAN (it has to be on orderable index, I mean, some kind of B-tree internal representation... but I'm not a specialist in MySQL).
Or mantain a separate table of registry PKs with expire_date. So premium user records will not ocuppy space. Then you do a delete from xxx where pk in (select pk from the expiring_table).
But that's not a very good enhance over the previous solution.
Maybe you can use ages (by example 1 age = 1 month). And set a field "month_to_live" in the table. Each month you update the field += 1 for all records that are not null. That uses an equality filter. But as I said before, I'm not a specialist so I don't know how much optimization you can gain from that).
I should insist in the expire_date field (and you have the extra capability of extend the live period to anyone, individually, without incurring in extra overhead when you delete records).
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]