How to write efficient MySQL query to delete specific rows depending on constraints


Problem :

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):

Games,

 id=INT
 play_date=DATETIME
 end_score=INT
 player_id_1=INT
 player_id_2=INT

Users,

 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...



Solution :

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).

EDIT

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.

Ages

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).


Mysql Tutorials

Mysql Howto..

MySQL: How to insert all columns from table to new table and NULL for extra columns not matched?

How does sorting on enums actually work?

Python: How to use a generator to avoid sql memory issue

How to handle large database?

How to add Hyperlink on php/html table that displays information from mysql database [closed]

How to interface a java application with a local database in Mac os x

how to INSERT into a table if no WARNING_COUNT, Mysql,php

MYSQL - sort a list, how to always have one result at the end?

How to get vi keybindings to work in mysql client?

Counting how many times each unique element appeared in the table

How to set autoreconnect option with mysql connector c++

How to get last 12 digits from a string in MySQL?

How to make value in ascending order in the mysql?

How do I output all rows of MySQL table and include a column for counting duplicates?

how to retrive record in textbox from mysql table with next and previous button

how to pass a value of table to a variable of c++ using select in mysql++ [closed]

How to clean UTF-8 data for MySQL

How to select 2 sub records using MySQL?

How to use a SQL Delimiter through powershell

How can I return LastInsertID from PDO whin a method of a class

How to get the unformated timestamp from mySQL table?

How to reverse a MySQL result

how to combine the result of one Mysql (sub)query that returns multiple rows and use that for another query

How to create UniDac MySQL components in console application?

How to order a MySQL query by range?

How to fix “Incorrect string value” errors?

MySQL Workbench - how to display relationships?

How to get Count in Hibernate using JPA?

How to find the Server Name of MySQL

How to update a table automatically as another table is updated on different mysql server?