title: How to write efficient MySQL query to delete specific rows depending on constraints
body: <p>I got a question regarding my MySQL-database and would like to get input on what would be most efficient.</p>
  
  <p>My problem is as follows,</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>Simplifying things I got two tables (in reality there's one more table which stores the game participants for each game):</p>
  
  <p>Games,</p>
  
  <pre><code> id=INT
   play_date=DATETIME
   end_score=INT
   player_id_1=INT
   player_id_2=INT
  </code></pre>
  
  <p>Users,</p>
  
  <pre><code> id=INT
   premium=BOOLEAN (true=enabled, false=not enabled)
  </code></pre>
  
  <p>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.</p>
  
  <p>What would be the most efficient way to remove games older than 18 months from NON-premium users.</p>
  
  <p>So far we've removed games older than 18 months for ALL users each Monday morning. </p>
  
  <p>Now I need to take premium-value and game date into account.</p>
  
  <p>A few solutions(?):</p>
  
  <ul>
  <li>JOIN'ing the tables, altough we're talking million of rows in the Games table, this would be a no-no?</li>
  <li>Get each game-entry older than 18 months, then get each users entry from player_id_1 &amp; 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.</li>
  <li>Above solution except I do it every hour. Then there's approx 1000 games I need to get and check.</li>
  <li>?? add some kind of helper variable to the Games table? But what if a user stops using premium....</li>
  </ul>
  
  <p>Any tips welcome...</p>