title: Database: Best practice - old data?
body: <p>I have a database of automobile classified listings.</p>
  
  <p>After 90 days, the classified listing is no longer valid to be displayed (the listing expires); however, I want to retain the listing for archive purposes.</p>
  
  <p><strong>Question</strong>: From a database design best practice perspective as well as query performance, is it better to keep the old listing <strong>A)</strong> in the same table as the current listing or <strong>B)</strong>, move the expired listing over to an expired table and delete that listing from the current listing table?</p>
  
  <p>In other words,</p>
  
  <p>Option <strong>A)</strong>:</p>
  
  <pre><code>table_classified_listing:
  car_id
  expired = true | false
  ...
  </code></pre>
  
  <p>Option <strong>B)</strong>:</p>
  
  <pre><code>// only current listing in this table (expired = false)
  table_classified_listing:
  car_id
  ...
  
  // only expired listing in this table (expired = true)
  expired_table_classified_listing:
  car_id
  ...
  </code></pre>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>My concern with Option A, is that in my MySQL database - when I run <code>EXPLAIN</code>, it say that it's using the <code>expired</code> as the primary key to index on. However, what's more important to my query search performance is for it to use the <code>price</code> field since I'm doing a search based on the <code>price &gt; X</code>. Hence why I'm considering to chose option B.</p>