Clustered indexes, mysql and Rails -

i'm helping rails application, intent application multi-tenanted. means there data multiple users/organisations in database tables, , access path along lines of "get me data organisation".

we're using mysql database.

rails default creates primary key on table using id column. id column auto-incremented. nice in ways - rows added @ end of table. however, consider following situation:

• an object called foo. foo has id, , has organisation_id
• over time each organisation creates foos in database, these foos interleaved throughout table (they stored in id sequence)
• a use case involves listing foos organisation

the problem have foos organisation not located closely in database, in fact they're spread around sub-optimally. ideally i'd create primary key of (organisation_id, id) on table, result in foos given organisation being side side in table.

unfortunately, when rails gives me 'unknown primary key table foos in model foo' error. think deal using composite keys gem rails, seems there should way make transparent @ database level.

is there alternate approach?

for reference, command on database change index was:

alter table foos add key (id); # needed because id column auto-increment

alter table foos drop primary key, add primary key(organisation_id, id);

edit 1: blog post indicates success doing composite_primary_keys gem. gives me bit more confidence approach, problem it's 2008, things may have moved on. http://www.joehruska.com/?p=6

edit 2: option considering partitioning instead - number of organisations wouldn't exceed maximum partitions, , group them bit without losing benefit. unfortunately, key quote every unique key on table must use every column in table's partitioning expression. (this includes table's primary key - mysql manual http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html.

so i'm still needing composite primary key again. i'm little surprised rails cares primary key, rather key present.

if don't want use composite_primary_keys may stuck relying on standard index on :organisation_id or [:organisation_id, :id] understanding rails cares primarykeys because of assumptions makes relationships between models. perhaps should improved, suggest future feature.