title: MySQL table partitioning on timestamp
body: <p>I have partitioned a table (because of an out of memory error - table got too big).  I have partitioned it on a timestamp column as shown below:</p>
  
  <pre><code>CREATE TABLE test (
      fname VARCHAR(50) NOT NULL,
      lname VARCHAR(50) NOT NULL,
      dob timestamp NOT NULL
  )
  PARTITION BY RANGE( unix_timestamp(dob) ) (
      PARTITION p2012 VALUES LESS THAN (unix_timestamp('2013-01-01 00:00:00')),
      PARTITION p2013 VALUES LESS THAN (unix_timestamp('2014-01-01 00:00:00')),
      PARTITION pNew VALUES LESS THAN MAXVALUE
  );
  </code></pre>
  
  <p>I was hoping that the process of partitioning would also help in speeding up a couple of my queries whihc take a few hours to run; however, this type of partitioning doesn't seem to kick in and all partitions are still being used and scanned through for the queries.  I have tried, and failed, with a couple more approaches:</p>
  
  <p>1) Tried to use different range for the partitioning</p>
  
  <pre><code>CREATE TABLE t2 (
      fname VARCHAR(50) NOT NULL,
      lname VARCHAR(50) NOT NULL,
      region_code TINYINT UNSIGNED NOT NULL,
      dob timestamp NOT NULL
  )
  PARTITION BY RANGE( YEAR(dob) ) (
      PARTITION p2012 VALUES LESS THAN (2013),
      PARTITION p2013 VALUES LESS THAN (2014),
      PARTITION pNew VALUES LESS THAN MAXVALUE
  );
  </code></pre>
  
  <p>However, that results in an error: <code>Error Code: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed</code></p>
  
  <p>2) Gave up on changing partitioning to be recognized by the query optimizer, and as suggested in MySQL's Doc - <a href="http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html" rel="nofollow">18.5 Partition Selection</a> tried specifying which partitions to use in the select statement instead:</p>
  
  <pre><code>select * from t2 partition (p2012) 
  </code></pre>
  
  <p>But, that returns a syntax error <code>Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p2012) LIMIT 0, 1000' at line 1</code></p>
  
  <p>Does anybody have any suggestions what else I could try to utilize table partitioning to optimize the queries?</p>