title: Determining partitioning key in range based partitioning of a MySQL Table
body: <p>I've been researching for a while regarding database partitioning in MySQL. Since I have one ever-growing table in my DB, I thought of using partitioning as an effective tool to optimize it. I'm only interested in retaining recent data (say last 6 months) and the table has a column name 'CREATED_AT' (TIMESTAMP, NON-PRIMARY), the approach which popped up in my mind is as follows</p>
  
  <ul>
  <li>Create a time-based range partition on the table by using 'CREATED_AT' as the partition key.</li>
  <li>Run a DB level Event periodically and drop partitions which are obsolete. ( older than 6 months).</li>
  </ul>
  
  <p>However, the partition can only be realized if I make 'CREATED_AT' field as primary. But doesn't it violate the primary key principle? since the same field is non-unique and can have tons of rows with the same value, doesn't marking it as primary turn out to be an anti-pattern? Is there any workaround to acheive time based ranged partitioning in this scenario?</p>