Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
I guess some experimenting wouldn't hurt on my end. Thanks. –  user962449 Dec 10 '12 at 22:00
    
You're welcome. Don't forget to check back here with your findings. Partitioning is deep water for most of us and test data is valuable.. –  Bing Dec 11 '12 at 9:30
add comment

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 )
);
share|improve this answer
1  
I get what you're saying, but I'm wondering about dropping the primary key from the auto-increment column. MySQL recommends to have all foreign keys identical. Does that only apply to datatypes? or will dropping the primary key have significant changes. Having no primary would create a hidden clustered index, and wouldn't that just be wasting space? –  user962449 Dec 13 '12 at 6:20
    
Just remember that Partitioned tables do not support foreign keys –  Alfie John Dec 13 '12 at 22:00
    
They don't support foreign constraints, but I was referring to the face that MySQL recommends having the same datatype columns when doing joins. –  user962449 Dec 14 '12 at 0:19
    
Optimally you want to use the same datatypes to avoid casting. Also, here's a good link on primary keys InnoDB - fromdual.com/… –  Alfie John Dec 14 '12 at 0:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.