title: How to partition MySQL table by day?
body: <p>I'm running MySQL 5.1 and storing data from web logs into a table. There is a datetime column which I want to partition by day. Every night I add new data from the previous day into the table, which is why I want to partition by day. It is usually a few million rows. I want to partition by day because it usually takes 20 seconds for a MySQL query to complete.</p>
  
  <p><strong>In short, I want to partition by each day because users can click on a calendar to get web log information consisting of a day's worth of data. The data spans millions of row (for a single day).</strong></p>
  
  <p>The problem that I've seen with a lot of partitioning articles is that you have to explicitly specify what values you want to partition for? I don't like this way because it means that I'll have to alter the table every night in order to add an extra partition. Is there a built in MySQL feature to do this for me automatically, or will I have to write a bash script/cron job to alter the table for me every night?</p>
  
  <p>For example, if I were to follow the following example:
  <a href="http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html" rel="nofollow">http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html</a></p>
  
  <p>In one year, I would have 365 partitions.</p>