title: Delete optimization using mysql partitioned table (MySQL 5.1)
body: <p>I have a log table that gets processed every night.  Processing will be done on data that was logged yesterday. Once the processing is complete I want to delete the data for that day.  At the same time, I have new data coming into the table for the current day.  I partitioned the table based on day of week.  My hope was that I could delete data and insert data at the same time without contention.  There could be as many as 3 million rows of data a day being processed.  I have searched for information but haven't found anything to confirm my assumption.<br>
  I don't want to have the hassles of writing a job that adds partitions and drop partitions as I have seen in other examples.  I was hoping to implement a solution using seven partions.  eg.</p>
  
  <pre><code>    CREATE TABLE `professional_scoring_log` (
        `professional_id` int(11) NOT NULL,
        `score_date` date NOT NULL,
        `scoring_category_attribute_id` int(11) NOT NULL,
        `displayable_score` decimal(7,3) NOT NULL,
        `created_at` datetime NOT NULL,
        PRIMARY KEY (`professional_id`,`score_date`,`scoring_category_attribute_id`),
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      /*!50100 PARTITION BY RANGE (DAYOFWEEK(`score_date`))
      (PARTITION Sun VALUES LESS THAN (2) ENGINE = InnoDB,
       PARTITION Mon VALUES LESS THAN (3) ENGINE = InnoDB,
       PARTITION Tue VALUES LESS THAN (4) ENGINE = InnoDB,
       PARTITION Wed VALUES LESS THAN (5) ENGINE = InnoDB,
       PARTITION Thu VALUES LESS THAN (6) ENGINE = InnoDB,
       PARTITION Fri VALUES LESS THAN (7) ENGINE = InnoDB,
       PARTITION Sat VALUES LESS THAN (8) ENGINE = InnoDB) */
  </code></pre>
  
  <p>When my job that processes yesterday's data is complete, it would delete all records where score_date = current_date-1.  At any one time, I am likely only going to have data in one or two partitions, depending on time of day.</p>
  
  <p>Are there any holes in my assumptions?  </p>