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
5% of my queries use the id under the
none of my queries use both columns under the
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.