Best way to organize compound indexes on partitioned table (date by range)?

Question

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.

Since MySQL creates an automatic index on the primary key (I'm assuming this), does this mean that since my date column is not on the left part of the index, then would it be beneficial to create a single index on just the date column?

95% of my queries use the date column under WHERE 5% of my queries use the id under the WHERE clause none of my queries use both columns under the WHERE clause

I used to have just a single index on the date column, but since I'm using partitions I'm forced to add the date column to the primary key. Should I create a single index on the date column? I'm asking this because my tables are big (30GB) and I don't want to add unnecessary overhead.

Answer 1

Yes, in order to use the date column efficiently you need the date first in the index - or as you suggest, by itself. If you can sacrifice auto increment, you can change the index order to (date,id), but then you'd need another index for id for the 5% queries on them. Probably your best bet is an extra index for date.

Ref: http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

"MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on"

Furthermore - you can test the way MySQL uses indexes by prefixing your queries with "explain", but you probably knew that already. This way you can try the variants and evaluate them.

Answer 2

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 )
);