Best way to organize compound indexes on partitioned table (date by range)?
I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date column.


Not necessarily. Since AUTO_INCREMENT columns don't have to be PRIMARY KEYs, you could instead do something like the following and the AUTO_INCREMENT column would still work.

ALTER TABLE myTable DROP PRIMARY KEY, ADD KEY(myAutoIncrementColumn);



  Should I create a single index on the date column?


You have queries that are doing lookups on the AUTO_INCREMENT column, so I would still keep its index in place (PRIMARY or without). However since 95% of your queries are hitting the date column, you're going to want to add a secondary index:

ALTER TABLE myTable ADD KEY (myDateColumn);


If you changed the AUTO_INCREMENT column from being a PRIMARY KEY to simple a KEY index like I mentioned above, your date column isn't part of any non-unique indexes anymore, so you can now create the partition:

ALTER TABLE myTable
PARTITION BY RANGE ( UNIX_TIMESTAMP(myDateColumn) ) (
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-01-01') ),
  PARTITION p1 VALUES LESS THAN ( MAXVALUE )
);