Partitioning Limitations

[Premal Shah]

I was trying to create a partition on a table which has 100 million rows and it keeps growing every day. I have an auto incrementing primary key on this table since this is a many to many relation table. I have a unique index (container_type_id, media_type_id). There are only a handful of media_type_id in this table (arnd 15). Now, its kinda impossible to create a partition on media_type_id since its not a part of the primary key. This is a huge huge limitation. I want to understand from the mysql crew as to why this is a limitation. Micrsoft SQL server does not have it. I am almost sure Oracle does not have it. Why does mysql have it. We are using django as our development framework. Django does not support multi-column primary keys. Even if it did, it does not make sense to make the primary key a composite of the auto-incrementing column and the media_type_id. I am not venting for the sake of it, but would like to understand why?

Thanx.



Edited 1 time(s). Last edit at 09/18/2009 11:59AM by Premal Shah.